mongodb $lookup has_many association from embedded document

Question

I have a boards collection, a lists collection, and a cards collection. An array of lists is embedded in a board document. I am trying to get an output that looks like this:

{
    _id: 1,
    title: "a board",
    lists: [
      { 
        _id: 1, 
        title: "a list", 
        cards: [ { _id: 1, title: "a card", list_id: 1 }, { _id: 2, title: "another card", list_id: 1 } ] 
      },
      ...
    ]
  }

I want to nest the cards in the list it belongs to. The card document has a list_id field. I tried this:

db.boards.aggregate([
  { '$match' => { _id: 1 } },
  { '$lookup' => {
    from: "cards",
    localField: "lists._id",
    foreignField: "list_id",
    as: "cards"
  } },
])

But that resulted in:

{
  _id: 1,
  title: "a board",
  lists: [ {  _id: 1, title: "a list" } ],
  cards: [ { _id: 1, title: "a card", list_id: 1 }, { _id: 2, title: "another card", list_id: 1 } ]
}

I know that I have to use $unwind to get the result I want, but I can't get it to work

If you use `foreignField: "list_id"` then why it isn't present in `cards` array? — mickl, Jul 06 '20 at 18:12

score 1 · Accepted Answer · answered Jul 06 '20 at 18:46

You need one additional aggregation pipeline step to "merge" these two lists and you can achieve it by running $map with embedded $filter. It simply works as a "join" operation on two arrays:

{
    $project: {
        _id: 1,
        title: 1,
        lists: {
            $map: {
                input: "$lists",
                as: "list",
                in: {
                    $mergeObjects: [
                        "$$list",
                        {
                            cards: {
                                $filter: {
                                    input: "$cards",
                                    cond: { $eq: [ "$$this.list_id", "$$list._id" ] }
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}

Mongo Playground

mongodb $lookup has_many association from embedded document

1 Answers1