Use $addFields to add field to each item in $lookup results-CodePudding

I have two collections in my MongoDB database, category and product, there is a One-to-many relation between these two. So I get what I want like this:

db.category.aggregate([
  {
    "$lookup": {
      "from": "product",
      "localField": "_id",
      "foreignField": "category_id",
      "as": "products"
    }
  }
])

It works perfectly fine, now I want to add a new field id equal to _id to each product in the products array. After many hours of research this is the closest thing I could find:

db.category.aggregate([
  {
    "$lookup": {
      "from": "product",
      "localField": "_id",
      "foreignField": "category_id",
      "as": "products"
    }
  },
  {
    $addFields: {
      "id": "$_id",
      "product.id": "$products._id"
    }
  },
  
])

Which will add id field but it's value is equal to an array containing ALL the product _ids !

I created this in Mongo Playground in case you want to check it out: https://mongoplayground.net/p/bQy_yf0c52h

Any help is highly appreciated. Thanks

CodePudding user response：

You need to use full $lookup syntax and add the field within the subquery:

db.category.aggregate([
  {
    "$lookup": {
      "from": "product",
      "let": {
        "id": "$_id"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              "$eq": [
                "$category_id",
                "$$id"
              ]
            }
          }
        },
        {
          "$addFields": {
            "id": "$_id"
          }
        }
      ],
      "as": "products"
    }
  },
  
])