MongoDB nested lookup in Embedded Document

Question

I have these collections

purchaseorder

{
  "_id": "5f0104a8d0c3a06fc9c06625",
  "purchaseItems": [
    {
      "product": "5eff29e9e2708a0ca980762e",
      "quantity": 1
    },
    {
      "product": "5eff29e9e2708a0ca980762e",
      "quantity": 2
    }
  ],
  "totalQuantity": 0,
  "documentDate": {
    "$date": "2020-07-04T16:00:00.000Z"
  }
}

and

product

{
  "_id": "5eff29e9e2708a0ca980762e",
  "name": "name",
  "code": "code",
  "cost": "1",
  "srp": "1",
  "minimum": "1",
  "startEffectiveDate": {
    "$date": "2020-07-04T16:00:00.000Z"
  }
}

How do I join the product field inside the purchaseItems which is an Embedded Array Document to the product collection. I need something like this.

{
  "_id": "5f0104a8d0c3a06fc9c06625",
  "purchaseItems": [
    {
      "product": {
        "_id": "5eff29e9e2708a0ca980762e",
        "name": "product name"
      },
      "quantity": 1
    },
    {
      "product": {
        "_id": "5eff29e9e2708a0ca980762e",
        "name": "product name 2"
      },
      "quantity": 1
    }
  ],
  "totalQuantity": 0,
  "documentDate": {
    "$date": "2020-07-04T16:00:00.000Z"
  }
}

I tried using this aggregation but I got an incorrect result

[{$lookup: {
  from: 'product',
  'let': {
    purchaseItems: '$purchaseItems'
  },
  pipeline: [
    {
      $lookup: {
        from: 'product',
        'let': {
            product: '$product'
          },
        "pipeline": [
           { "$match": { "$expr": { "$eq": [ "$_id", "$$product" ] } } }
         ],
         "as": "product"  
      }
    }
  ],
  as: 'purchaseItems'
} }]

Not really familiar with mongodb and I'm already considering going back to SQL.

Answer 1

Don't give up. We can obtain the desired result with 2 solutions:

1. Standard $lookup + $project

Explanation

a. $map allows us iterate over an array and transform it (add/keep/remove fields)
b. $filter allows to get the matched items from an array
c. Since $filter returns an array, we need to use the $arrayElemAt operator to get a single item or apply $unwind

---

db.purchaseorder.aggregate([
  {
    "$lookup": {
      "from": "product",
      "localField": "purchaseItems.product",
      "foreignField": "_id",
      "as": "product"
    }
  },
  {
    $project: {
      documentDate: 1,
      totalQuantity: 1,
      purchaseItems: {
        $map: {
          input: "$purchaseItems",
          as: "item",
          in: {
            product: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$product",
                    as: "prod",
                    cond: {
                      $eq: [
                        "$$prod._id",
                        "$$item.product"
                      ]
                    }
                  }
                },
                0
              ]
            },
            quantity: "$$item.quantity"
          }
        }
      }
    }
  }
])

MongoPlayground | Removing extra fields from product

2. Uncorrelated sub-query with $lookup

Explanation

Since MongoDB v3.6 $lookup allows perform non-standard query to join 2 or more collections
a. We pass purchaseItems inside lookup pipeline
b. Perform match by product _id values.
c. Applying $filter, we get only related quantity values
d. We flatten the quantity field with the $unwind operator (remember $filter returns an array)
e. We transform to the desired output with $project stage

---

db.purchaseorder.aggregate([
  {
    "$lookup": {
      "from": "product",
      "let": {
        purchaseItems: "$purchaseItems"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$purchaseItems.product"
              ]
            }
          }
        },
        {
          $addFields: {
            quantity: {
              $filter: {
                input: "$$purchaseItems",
                as: "purch",
                cond: {
                  $eq: [
                    "$$purch.product",
                    "$_id"
                  ]
                }
              }
            }
          }
        },
        {
          $unwind: "$quantity"
        },
        {
          $project: {
            _id: 0,
            product: {
              _id: "$_id",
              name: "$name"
            },
            quantity: "$quantity.quantity"
          }
        }
      ],
      "as": "purchaseItems"
    }
  }
])

MongoPlayground