How to add quantity field in lookup data

Question

How to add quantity field in my product lookup array?

My order model:

{
    user: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "user",
    },
    orderItems: [
          {
            product:{ 
              type: mongoose.Schema.Types.ObjectId,
              ref: "products",
            },
            quantity: {type :Number, required:true}
          }  
    ],
},

below here: mongo aggregation I only get user details and products details of array. But I want quantity in products details array

Order.aggregate([
    { $lookup: {  from: 'users',
                localField: 'user',
                foreignField: '_id',
                as: 'user'
                }
    },
    { $unwind: '$orderItems' },
    {
        $project: {
            quantity: 0
        }
    },
    {$lookup : {from : 'products', localField: 'orderItems.product', foreignField: '_id', as : 'product'}},
    { $unwind: '$product' },
    {$group : { _id: '$_id', user:{'$first':'$user'},products: {$push: '$product'}}}
])

Upper mongo aggregation I only get user details and products details of array. But I want quantity in products details array.

How can I get my result like this?

{
    "_id": "645e126a3ea9894242f39714",
    "user": [
      {
        "_id": "645ac718b6f8448ef0e530b2",
        "firstName": "abcemail",
        "lastName": "String",
        "email": "abc@gmail.com",
      }
    ],
    "products": [
            {
                "productId": {
                    "_id": "63b16fc58fe585c7b81c748d",
                    "title": "asd",
                    "price": "123"
                },
                "quantity": 3,
                "_id": "63b1b4de5a95bd4df7f9443c"
            },
            {
                "productId": {
                    "_id": "63b16fc58fe585c7b81c748d",
                    "title": "asd",
                    "price": "123"
                },
                "quantity": 3,
                "_id": "63b1b4de5a95bd4df7f9443c"
           }
    ]
  }

Answer 1

Here's one way you could do it. Comments are in the pipeline.

db.Order.aggregate([
  { // get user details
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user"
    }
  },
  { // get all product details
    "$lookup": {
      "from": "products",
      "localField": "orderItems.product",
      "foreignField": "_id",
      "as": "products"
    }
  },
  {
    "$project": {
      "user": 1,
      "products": {
        "$map": {
          "input": "$products",
          "as": "product",
          "in": {
            // format each object as desired
            // redundant _id
            "_id": "$$product._id",
            // full product details
            "productId": "$$product",
            // get quantity for this product
            "quantity": {
              "$getField": {
                "field": "quantity",
                "input": {
                  "$first": {
                    "$filter": {
                      "input": "$orderItems",
                      "as": "item",
                      "cond": {"$eq": ["$$item.product", "$$product._id"]}
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.

Here's another way by "$map"'ing the "orderItems" array rather than the "products" array. Perhaps it's a bit more simple/straightforward.

db.Order.aggregate([
  { // get user details
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user"
    }
  },
  { // get all product details
    "$lookup": {
      "from": "products",
      "localField": "orderItems.product",
      "foreignField": "_id",
      "as": "products"
    }
  },
  {
    "$project": {
      "user": 1,
      "products": {
        "$map": {
          "input": "$orderItems",
          "as": "item",
          "in": {
            "$mergeObjects": [
              "$$item",
              {
                "productId": {
                  "$first": {
                    "$filter": {
                      "input": "$products",
                      "as": "product",
                      "cond": {"$eq": ["$$item.product", "$$product._id"]}
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.

Here's another way you could do it by first "$unwind"'ing.

db.Order.aggregate([
  {
    "$unwind": "$orderItems"
  },
  {
    "$lookup": {
      "from": "products",
      "localField": "orderItems.product",
      "foreignField": "_id",
      "as": "products"
    }
  },
  {
    "$set": {
      "products": {
        "$first": "$products"
      }
    }
  },
  {
    "$project": {
      "user": 1,
      "products": {
        "productsId": "$products",
        "quantity": "$orderItems.quantity",
        "_id": "$products._id"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "user": {"$first": "$user"},
      "products": {"$push": "$products"}
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user"
    }
  }
])

Try it on mongoplayground.net.