CODEWITHSUNDEEP
mongodbexpressmongooseaggregation-framework
Amani
Amani

Reputation: 247

Mongoose - Finding an item in subdocument array together with the whole total in that array

I have a Shop collection with a document like this:

{
  _id:'xyz'
  sales:[
    {
      _id:'aaa'
      productId:'ppp',
      quantity: 3
    },
    {
     _id:'bbb'
     productId:'qqq'
     quantity: 4
   },
  ]
}

When i query an element in sales array (_id:'aaa') i need to get its quantity and the sum of quantity in the whole sales array ie quantity:3, totalQuantity: 7

Same wise, when i query another element (_id:'bbb') i need to get quantity: 4, totalQuantity: 7

I have tried using aggregate, as follows:

Shop.aggregate([
        {$match: {'_id': ObjectId('xyz'),'sales._id':ObjectId('aaa')}},
        {$group: {
          _id: "$_id",
          totalQuantity: {
            "$sum": {
              "$sum": "$sales.quantity"
            }
          }
        }
 ])

But this doesn't work.

How can I achieve this?

Upvotes: 0

Views: 125

Answers (1)

SuleymanSah
SuleymanSah

Reputation: 17898

We can get the totalQuantity using $sum.

We can use $filter to find the required item, and access the quantity.

Playground

db.collection.aggregate([
  {
    $match: {
      _id: "xyz"
    }
  },
  {
    $project: {
      totalQuantity: {
        $sum: "$sales.quantity"
      },
      quantity: {
        $filter: {
          input: "$sales",
          cond: {
            $eq: [
              "$$this._id",
              "aaa"
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      quantity: {
        $sum: "$quantity.quantity"
      }
    }
  }
])

This will give the following result for the "aaa" value:

[
  {
    "_id": "xyz",
    "quantity": 3,
    "totalQuantity": 7
  }
]

And for a not found value, the quantity will be 0:

[
  {
    "_id": "xyz",
    "quantity": 0,
    "totalQuantity": 7
  }
]

Upvotes: 1

Related Questions