'lookup on all documents inside an array - MongoDB aggregation

I need to execute a lookup stage on all documents inside an array.

The collection:

{
  {
    "name": "test",
    "age": 2,
    "replies": [
        {
            "title": "testtt",
            "merta_id": 1
        },
        {
            "title": "testiona",
            "merta_id": 5
        },
        {
            "title": "the thirth test",
            "merta_id": 4
        }
    ]
    
  }
}

The mertas collection:

{
  {
     _id: 1,
     a: "aaaa",
     b: "bbbb"
  },
  {
     _id: 5,
     a: "AaAA",
     b: "BbbB"
  },
    {
     _id: 4,
     a: "Aou",
     b: "Boo"
  }
}

Expected output:

{
  {
    "name": "test",
    "age": 2,
    "replies": [
        {
            "title": "testtt",
            "merta_id": 1,
            "merta": {
                 _id: 1,
                 a: "aaaa",
                 b: "bbbb"
            }
        },
        {
            "title": "testiona",
            "merta_id": 5,
            "merta": {
                 _id: 5,
                 a: "aaaa",
                 b: "bbbb"
             }
        },
        {
            "title": "the thirth test",
            "merta_id": 4
            "merta":{
                  _id: 4,
                  a: "Aou",
                  b: "Boo"
              }
        }
    ]

  }
}

I need an aggregation stage to execute a lookup on all documents on "replies" and add a new merta field, which should lookup from mertas collection. I tried to use $map stage, but got the error "Unrecognized pipeline stage name: '$lookup'"

mongodbaggregation-frameworkaggregation


Solution 1:[1]

You can use below aggregation

db.collection.aggregate([
    { "$unwind": "$replies" },
    { "$lookup": {
        "from": "mertas",
        "localField": "replies.merta_id",
        "foreignField": "_id",
        "as": "replies.merta"
    }},
    { "$unwind": "$replies.merta" },
    { "$group": {
        "_id": "$_id",
        "data": { "$first": "$$ROOT" },
        "replies": { "$push": "$replies" }
    }},
    { "$replaceRoot": {
        "newRoot": {
            "$mergeObjects": ["$data", { "replies": "$replies" }]
        }
    }}
])

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions