Mongodb - Unwinding Nested Subdocuments

For the following dataset example: lists { _id: 1, included_lists: [ 2 ], items: [ 'i1' ]} { _id: 2, included_lists: [], items: [ 'i2', 'i3' ]} items { _id: 'i1', details: [{}, {}

Solution 1:

You can try below aggregation from mongodb 3.6 and above

List.aggregate([
  { "$match": { "_id": id }},
  { "$lookup": {
    "from": Items.collection.name,
    "let": { "items": "$items" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$items" ] } } }
    ],
    "as": "items"
  }},
  { "$lookup": {
    "from": Lists.collection.name,
    "let": { "included_lists": "$included_lists", "items": "$items" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$included_lists" ] } } },
      { "$lookup": {
        "from": Items.collection.name,
        "let": { "items": "$items" },
        "pipeline": [
          { "$match": { "$expr": { "$in": [ "$_id", "$$items" ] } } }
        ],
        "as": "items"
      }},
      { "$project": { "allItems": { "$concatArrays": [ "$$items", "$items" ]}}}
    ],
    "as": "included_lists"
  }},
  { "$unwind": "$included_lists" },
  { "$replaceRoot": { "newRoot": "$included_lists" }}
])

Solution 2:

You can try below aggregation in 3.4.

Initial $lookup to get the items values for included_lists followed by $concatArrays to merge the looked up items and items.

Second $lookup to get the item details followed by $unwind to flatten the results.

List.aggregate([
{"$lookup":{
  "from":name of the list collection,
  "localField":"included_lists",
  "foreignField":"_id",
  "as":"included_items"
}},
{"$unwind":"$included_items"},
{"$project":{"allItems":{"$concatArrays":["$items","$included_items.items"]}}},
{"$lookup":{
  "from":name of the item collection,
  "localField":"allItems",
  "foreignField":"_id",
  "as":"lookedup_items"
}},
{"$unwind":"$lookedup_items"},
{"$skip": some number},
{"$limit": some number}
])