How to lookup in the same document under a collection in MongoDB

Question

I am having below document in MongoDB [UPDATE : Shortened the document]

{
  "sections": [
    {
      "_id": {
        "$oid": "64cbeb62b669cd29a5719a8f"
      },
      "categories": [
        {
          "measureValues": [
            "64cbeb62b669cd29a5719a95",
            "64cbeb62b669cd29a5719a99"
          ]
        }
      ]
    }
  ],
  "measures": [
    {
      "_id": {
        "$oid": "64cbeb62b669cd29a5719a93"
      },
      "name": {
        "64cbeb62b669cd29a5719a92": {
          "nodeType": "nodeName",
          "value": "measure-1"
        }
      },
      "measureValues": [
        {
          "_id": {
            "$oid": "64cbeb62b669cd29a5719a95"
          },
          "measureValues": {
            "nodeName": {
              "64cbeb62b669cd29a5719a94": {
                "nodeType": "nodeName",
                "value": "measureValue",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            },
            "comment": {
              "64cbeb62b669cd29a5719a97": {
                "nodeType": "comment",
                "value": "this is a comment",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            }
          },
          "categoriesIds": [
            "64cbeb62b669cd29a5719a91"
          ],
          "measureName": "measure-1"
        },
        {
          "_id": {
            "$oid": "64cbeb62b669cd29a5719a99"
          },
          "measureValues": {
            "nodeName": {
              "64cbeb62b669cd29a5719a98": {
                "nodeType": "nodeName",
                "value": "measureValue",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            },
            "date": {
              "64cbeb62b669cd29a5719a9b": {
                "nodeType": "date",
                "value": "2023-02-02",
                "_class": "com.myapp.cqrsdemo.model.DataObj"
              }
            }
          },
          "categoriesIds": [
            "64cbeb62b669cd29a5719a91"
          ],
          "measureName": "measure-1"
        }
      ]
    }
  ],
  "_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}

I want to fetch the "measureValues" under "measures" where the id of the "measureValues" is matching with the id of the measureValues under categories. I want to include the measureValue object under categories instead of just the Ids.

How can I do that? I tried writing aggregate pipeline, but it failed.

My Output should look like below.

{
  "sections": [
    {
      "_id": {
        "$oid": "64cbeb62b669cd29a5719a8f"
      },
      "categories": [
        {
          "measureValues": [
            {
              "_id": {
                "$oid": "64cbeb62b669cd29a5719a95"
              },
              "measureValues": {
                "nodeName": {
                  "64cbeb62b669cd29a5719a94": {
                    "nodeType": "nodeName",
                    "value": "measureValue",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                },
                "comment": {
                  "64cbeb62b669cd29a5719a97": {
                    "nodeType": "comment",
                    "value": "this is a comment",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                }
              },
              "categoriesIds": [
                "64cbeb62b669cd29a5719a91"
              ],
              "measureName": "measure-1"
            },
            {
              "_id": {
                "$oid": "64cbeb62b669cd29a5719a99"
              },
              "measureValues": {
                "nodeName": {
                  "64cbeb62b669cd29a5719a98": {
                    "nodeType": "nodeName",
                    "value": "measureValue",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                },
                "date": {
                  "64cbeb62b669cd29a5719a9b": {
                    "nodeType": "date",
                    "value": "2023-02-02",
                    "_class": "com.myapp.cqrsdemo.model.DataObj"
                  }
                }
              },
              "categoriesIds": [
                "64cbeb62b669cd29a5719a91"
              ],
              "measureName": "measure-1"
            }
          ]
        }
      ]
    }
  ],
  "_class": "com.myapp.cqrsdemo.model.ProjectGroup"
}

Would you show your desired output and your aggregation pipeline attempts? — rickhg12hs, Aug 03 '23 at 23:11
Can you simplify the problem. It seems you have posted a large example document, where many of the fields play no role in your problem. These extra data points make it more difficult for someone to assist. #help-me-help-you — barrypicker, Aug 04 '23 at 00:46
@barrypicker - I have updated the shortened the input document. And also uploaded the desired output. — Pushpak, Aug 04 '23 at 04:33
Still a rather big document. And what is the pipeline you tried? What do you mean by "it failed"? — Wernfried Domscheit, Aug 04 '23 at 05:55
Having dynamic key names (like `64cbeb62b669cd29a5719a9b`) is usually a poor design, it makes your life more difficult. And it looks like you mix `ObjectId` (`$oid`) with string values, which is also not very smart. — Wernfried Domscheit, Aug 04 '23 at 09:28
With the given data it would be simply `db.collection.aggregate([{ $project: { _class: 1, sections: { categories: { $first: "$measures.measureValues" } } } }])` But you lack some information: Which array can be empty? Are the array elements always unique? It is also not clear which elements are defined as array despite they have always only one element (and thus should be better **not** stored as an array) — Wernfried Domscheit, Aug 04 '23 at 09:50

nimrod serok · Accepted Answer · 2023-08-04T06:18:58.400

One option is:

Create a var measureValuesArr and store inside it a single flatten array of all measureValues under all measures
Since the sections.categories.measureValues is a double nested array (inside an array on arrays) use double $map to get into all instances of it.
Use $arrayElemAt with $indexOfArray to find the matching measureValues item inside measureValuesArr

db.collection.aggregate([
  {$set: {
      sections: {$let: {
          vars: {measureValuesArr: {$reduce: {
                input: "$measures",
                initialValue: [],
                in: {$concatArrays: ["$$value", "$$this.measureValues"]}
          }}},
          in: {$map: {
              input: "$sections",
              as: "s",
              in: {$mergeObjects: [
                  "$$s",
                  {categories: {$map: {
                        input: "$$s.categories",
                        as: "c",
                        in: {$mergeObjects: [
                            "$$c",
                            {measureValues: {$map: {
                                  input: "$$c.measureValues",
                                  as: "m",
                                  in: {$arrayElemAt: [
                                      "$$measureValuesArr",
                                      {$indexOfArray: [
                                          "$$measureValuesArr._id",
                                          {$toObjectId: "$$m"}
                                      ]}
                                  ]}
                            }}}
                        ]}
                  }}}
              ]}
          }}
      }}
  }}
])

See how it works on the playground example

*This solution assumes that a matching object does exists somewhere under measures if there is an option that it does not exists, an handling of this case should be added to the query.

How to lookup in the same document under a collection in MongoDB

1 Answers1