Add looked up data into an array of objects

Question

There is a lot of information about MongoDB and its logic out there, but I was not able to find any example to point me in the correct direction with this problem. What I would like to do is return an order by ID and populate all products within that order with information looked up from the products collection. Returning the order is easy, but where I fail is when it comes to unwinding the array of objects and, especially, getting the looked up information back into the original object within the array.

Collection "orders":

{
    _id: ObjectId(...),
    products: [
        { product_id: ObjectId(...), config: { ... } },
        { product_id: ObjectId(...), config: { ... } },
        ....
    ]
},
...

Collection "products":

{
    _id: ObjectId(...),
    title: "Product 1",
    manufacturer: "Good Company",
},
{
    _id: ObjectId(...),
    title: "Product 2",
    manufacturer: "Another Company",
},
...

Desired Order-Output:

{
    _id: ObjectId(...),
    products: [
        { product_id: ObjectId(...), config: { ... }, title: ..., manufacturer: ... },
        { product_id: ObjectId(...), config: { ... }, title: ..., manufacturer: ... },
        ....
    ]
}

Here is what I've got so far:

db.collection('orders').aggregate([
    {
        $match: {
            // This part is tested and working well
            user_id: request.middleware.authentication.id,
            status: 'active'
        }
    },
    {
        $unwind: '$products'
    },
    {
        $lookup: {
            from: '$products',
            as: 'product_objects',
            let: { product_id: '$products.product_id' },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: [ '$$product_id', '$_id' ] }
                    }
                }
            ]
        }
    },
    {
        $unwind: {
            path: '$product_objects',
            preserveNullAndEmptyArrays: true
        }
    }
]);

The problems with it; a) it will only look up one product b) it will not insert the information into the objects in the products-array but create the new field product_objects instead. I feel like fixing this would be hours of trial and error - so if anyone is willing to help me figure this out, the help would be highly appreciated!

user_18482310 · Answer

One option is merge the array after the $lookup:

db.orders.aggregate([
  {$match: {_id: ObjectId("62c9a0f5dfb6cf9c7fe859f2")}},
  {$lookup: {
      from: "products",
      localField: "products.product_id",
      foreignField: "_id",
      as: "productsDetails"
    }
  },
  {$set: {
      products: {
        $map: {
          input: "$products",
          in: {$mergeObjects: [
              "$$this",
              {$arrayElemAt: [
                  "$productsDetails",
                  {$indexOfArray: ["$products._id", "$$this.product_id"]}
                ]
              }
            ]
          }
        }
      },
      productsDetails: "$$REMOVE"
    }
  }
])

See how it works on the playground example

Add looked up data into an array of objects

1 Answers