How to display sum of field from another document

[Sahil_Shrestha]

In my code, I have a collection called stock and another called product.

Stock holds the data in this format

{
  "_id": 123 // id
  "product_id":  ObjectId ('427875465348') // object id of a product
  "quantity": 50  // quantity added in the stock
}

And product Id stores in this format:

{
  "_id": "63327c0ba1d239a0dcec7d87", // id used in stock collection
  "name": "Rice", // name of product
  "category": "Vegetables", 
  "life": 20,
  "price": 22.9
}

Every time a stock is added a new record will be created in stocks table. The stock table can have multiple document with same product id.

I want to display all record of product in this way:

{
  "_id": "63327c0ba1d239a0dcec7d87", // id used in stock collection
  "name": "Rice", // name of product
  "category": "Vegetables", 
  "life": 20,
  "price": 22.9
  "quantity" : // sum of all the quantity of the document in stock relating to this product using 
                                                                                                         product_id
}

How I do that?

[steevej (Steeve Juneau)]

Can you provide a sample stock document that refers to your product?

The product_id:ObjectId(427875465348) does not match your _id from product.

Basically, you do the following UNTESTED aggregation

db.product.aggregate( [
  { "$match" : { "product_id" : CurrentProduct } } ,
  { "$lookup" : {
    "from" : "stock" ,
    "localField" : "_id" ,
    "foreignField" : "product_id" ,
    "pipeline" : [ { "$group" : { "_id":null , "quantity" : { "$sum" : "quantity" } } } ] ,
    "as" : "result" 
  } }
] )

[Pavel_Duchovny]

Hi @Sahil_Shrestha ,

A small correction to @steevej great pipeline example:

db.products.aggregate([{
 $match: {
  _id: '63327c0ba1d239a0dcec7d87'
 }
}, {
 $lookup: {
  from: 'stock',
  localField: '_id',
  foreignField: 'product_id',
  pipeline: [
   {
    $group: {
     _id: null,
     quantity: {
      $sum: '$quantity'
     }
    }
   }
  ],
  as: 'quantity'
 }
}, {
 $addFields: {
  quantity: {
   $first: '$quantity.quantity'
  }
 }
}]) 

This will allow you to reshape the data as wanted.