CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
xiaozhuai
xiaozhuai

Reputation: 85

mongodb $lookup 3 level nested document

I'm new to mongo and struggling mightily with the following. In my mongodb database, there are 3 collections and structured as below.

lv1: 

{
    "_id": ObjectId("58650f1abbf1cd8804d0abde"), 
    "name": "lv1_aaa"
}

lv2: 

{
    "_id": ObjectId("5ba45de41e78c7eb3fdfbfa6"), 
    "lv1_id": ObjectId("58650f1abbf1cd8804d0abde"), 
    "name": "lv2_bbb"
}

lv3: 

{
    "_id": ObjectId("5ba45de41e78c7eb3fdfbfa6"), 
    "lv1_id": ObjectId("58650f1abbf1cd8804d0abde"),
    "lv2_id": ObjectId("58d8c3e1bbf1cd7436117bd6"), 
    "name": "lv3_ccc"
}

How can I get a data structure below use $lookup

[
    {
        "_id": ObjectId("58650f1abbf1cd8804d0abde"), 
        "name": "lv1_aaa",
        "children": [
            {
                "_id": ObjectId("5ba45de41e78c7eb3fdfbfa6"), 
                "lv1_id": ObjectId("58650f1abbf1cd8804d0abde"), 
                "name": "lv2_bbb",
                "children": [
                    {
                        "_id": ObjectId("5ba45de41e78c7eb3fdfbfa6"), 
                        "lv1_id": ObjectId("58650f1abbf1cd8804d0abde"),
                        "lv2_id": ObjectId("58d8c3e1bbf1cd7436117bd6"), 
                        "name": "lv3_ccc"
                    },
                    ......
                ]
            },
            ......
        ]
    },
    ......
]

Any help would be greatly appreciated!

Upvotes: 2

Views: 368

Answers (1)

Ashh
Ashh

Reputation: 46441

You can try below aggregation with mongodb 3.6 and above

db.lv1.aggregate([
  { "$sort": { _id: 1 } },
  { "$lookup": {
    "from": "lv2",
    "let": { "lv1_id": "$_id" },
    "pipeline": [
      { "$sort": { index: 1 } },
      { "$match": { "$expr": { "$eq": [ "$lv1_id", "$$lv1_id" ] } } },
      { "$lookup": {
        "from": "lv3",
        "let": { "lv2_id": "$_id" },
        "pipeline": [
          { "$sort": { index: 1 } },
          { "$match": { "$expr": { "$eq": [ "$lv2_id", "$$lv2_id" ] } } }
        ],
        "as": "children"
      }}
    ],
    "as": "children"
  }}
]);

Upvotes: 2

Related Questions