I have an e-commerce server where I have a products and an orders collection.

Any product document contains a unique productId e.g. prod_123. Each order document contains a lineItems (array) field which returns the productIds of the purchased products as well as the respective quantity purchased e.g.

[{ productId: 'prod_123', quantity: 2 }, { productId: 'prod_234', quantity: 7 }, ...]

When my client fetches their orders, I want to populate the each of the lineItems elements' productId with the matching product document in the products collection.

I have written a mongoDB aggregation pipeline to achieve this, and this is it so far:

 const orderPipeline = [
    {
      $match: { customerId: 'the customer's ID' },
    },
    {
      $lookup: {
        from: 'products',
        let: { productIds: '$lineItems.productId' },
        pipeline: [
          { $match: { $expr: { $in: ['$productId', '$$productIds'] } } },
          //*** somehow, need to add in corresponding `lineItem.quantity` here
        ],
        as: 'products',
      },
    },
    { $unset: ['lineItems'] },
  ];

However, as you can see, though the join is taking place, I cannot work out how to add the matched product's quantity to the joined product before I remove lineItems.

How can I add the corresponding quantity to the corresponding matched product?

CodePudding user response：

One approach, that I'm pretty sure will work given the additional constraints mentioned in the comments, would be to leverage the $zip operator. Overall it would work like this:

1. Perform the $lookup generating an array (products) with the information retrieved from the other collection.
2. Use an $addFields stage as the place where most of the combination logic happens. It will $zip the two arrays together and then $map over it to $mergeObjects each of the pairs into a single object.
3. Finish with an $unset stage to remove the original lineItems field (which has already been merged into the recreated products array.

The full pipeline would look something like this:

db.orders.aggregate([
  {
    $match: {
      customerId: 123
    },
    
  },
  {
    $lookup: {
      from: "products",
      let: {
        productIds: "$lineItems.productId"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$productId",
                "$$productIds"
              ]
            }
          }
        }
      ],
      as: "products",
      
    }
  },
  {
    "$addFields": {
      "products": {
        "$map": {
          "input": {
            "$zip": {
              "inputs": [
                "$lineItems",
                "$products"
              ]
            }
          },
          "in": {
            "$mergeObjects": "$$this"
          }
        }
      }
    }
  },
  {
    $unset: "lineItems"
  }
])

Playground example here

The $map and the $mergeObjects: "$$this" probably look odd at first glance. This is needed because the $zip is going to generate an array of arrays (with 2 entries each), such as this:

    "zipped": [
      [
        {
          "productId": "a",
          "quantity": 1
        },
        {
          "_id": ObjectId("5a934e000102030405000002"),
          "productId": "a"
        }
      ],
      [
        {
          "productId": "b",
          "quantity": 2
        },
        {
          "_id": ObjectId("5a934e000102030405000003"),
          "productId": "b"
        }
      ],
      [
        {
          "productId": "c",
          "quantity": 3
        },
        {
          "_id": ObjectId("5a934e000102030405000004"),
          "productId": "c"
        }
      ]
    ]

(Here is a playground link that shows the output after zipping but before further processing.)

Because of this we need to collapse each of those into a single object, hence the $mergeObjects. And the fact that each object in the outer array is an array (with the two objects we want to merge) is why we can simply use "$$this" as the input expression for the operator.