lookup on array of Objects

Question

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"}
  ]
}

Yong Shun · Accepted Answer · 2021-11-21 02:34:56Z

2

$unwind - Deconstruct refs array field.
$lookup - Join collectionA (refs.id) with collectionB (_id).
$project - Decorate the document, take first name from refsB array via $first.
$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

edited Nov 21, 2021 at 2:34

answered Nov 20, 2021 at 13:33

Yong Shun

54.6k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yong Shun Over a year ago

Oops, thanks a lot Takis_ for the point out. Missing out the parent's name. =)

Vu Le Anh Over a year ago

it works. Thank you. I already added name like your update :)

Collectives™ on Stack Overflow

lookup on array of Objects

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related