I have a collection named users, and this is how one specific user will look like:

{
    _id: 'Object ID',
    name: 'String',
    cart: [
        {
            product_id: 'Product object ID',
            quantity: 'Number',
        },
        ...
    ],
}

I want my desired results to look like this:

{
    _id: 'Object ID',
    name: 'String',
    cart: [
        {
            product_id: 'Product object ID',
            quantity: 'Number',
            product_details: {
                'all the details of the product from Products collection which matches the product_id',
            },
        },
        ...
    ],
}

I tried adding addFields into lookup but it's getting too complicated and doesn't work as desired. What's the best way to aggregate this?

CodePudding user response：

You can achieve this in several different ways, here's what I consider to be the most simple:

db.users.aggregate([
  {
    "$lookup": {
      "from": "products",
      let: {
        cart: "$cart"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$cart.product_id"
              ]
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: {
              "$mergeObjects": [
                "$$ROOT",
                {
                  "$arrayElemAt": [
                    {
                      $filter: {
                        input: "$$cart",
                        cond: {
                          $eq: [
                            "$_id",
                            "$$this.product_id"
                          ]
                        }
                      }
                    },
                    0
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "cart"
    }
  }
])

Mongo Playground