How to lookup with object by reference id and other properties.

collectionA

{
  _id: ObjectId("6013859ba0c3120034d08bfa"),
  name: "A1",
  refs:[
   {id: ObjectId("6013859ba0c3120034d08bfb"), text: "ABC"},
   {id: ObjectId("6013859ba0c3120034d08bfc"), text: "DEF"}
  ]
}

collectionB

{
  _id: ObjectId("6013859ba0c3120034d08bfb"),
  name: "B1"
}

{
  _id: ObjectId("6013859ba0c3120034d08bfc"),
  name: "B2"
}

Expected Result

{
  _id: ObjectId("6013859ba0c3120034d08bfa"),
  name: 'A1',
  refs:[
   {id: ObjectId("6013859ba0c3120034d08bfb"), name: "B1", text: "ABC"},
   {id: ObjectId("6013859ba0c3120034d08bfc"), name: "B2", text: "DEF"}
  ]
}

CodePudding user response：

1. $unwind - Deconstruct refs array field.
2. $lookup - Join collectionA (refs.id) with collectionB (_id).
3. $project - Decorate the document, take first name from refsB array via $first.
4. $group - Group by _id and generate (required) fields for the document(s).

db.collectionA.aggregate([
  {
    $unwind: "$refs"
  },
  {
    "$lookup": {
      "from": "collectionB",
      "localField": "refs.id",
      "foreignField": "_id",
      "as": "refsB"
    }
  },
  {
    $project: {
      _id: 1,
      name: 1,
      refs: {
        id: "$refs.id",
        text: "$refs.text",
        name: {
          $first: "$refsB.name"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      refs: {
        $push: "$refs"
      }
    }
  }
])

Sample Mongo Playground