Aggregate based on Array value

Question

I have collection schema like :

{
"_id" : ObjectId("582ee289618a504e5c830e03"),
"updatedAt" : ISODate("2016-11-24T05:01:59.470Z"),
"createdAt" : ISODate("2016-11-18T11:14:17.912Z"),
"requestId" : "IER5R2H",
"pickupDetails" : {
    "_id" : ObjectId("58367447b0a1ada74ad7af7e"),
    "itemsCount" : 1,
    "pickupItems" : [
        {
            "name" : "Newspaper",
            "quantity" : 15,
            "unit" : "kg",
            "unitPrice" : 9,
            "amount" : 135,
            "_id" : ObjectId("58367447b0a1ada74ad7af7f")
        }
    ]
}}

{
"_id" : ObjectId("58fff31a618a504e5c831191"),
"updatedAt" : ISODate("2016-11-21T13:37:51.267Z"),
"createdAt" : ISODate("2016-11-19T06:37:14.857Z"),
"requestId" : "M7OZY9O",
"pickupDetails" : {
    "_id" : ObjectId("5832f8afb8ec77fa3c518f97"),
    "itemsCount" : 2,
    "pickupItems" : [
        {
            "name" : "Newspaper",
            "quantity" : 18,
            "unit" : "kg",
            "unitPrice" : 11,
            "amount" : 198,
            "_id" : ObjectId("5832f8afb8ec77fa3c518f98")
        },
        {
            "name" : "Plastic",
            "quantity" : 4,
            "unit" : "kg",
            "unitPrice" : 11,
            "amount" : 44,
            "_id" : ObjectId("584a853e46c71be3585bfb5a")
        }

    ]
}}

I need to add the quantity based on the name of pickupItems, like "Newspaper" etc. Suppose quantity of Newspaper for a particular day. As per the below data, result should be like, {"Newspaper":33}, for a particular date.

is there possibility of two newspaper in same array(like with different unitPrice)? and you want count of occurrence? or sum of quantity? — Mohsen ZareZardeyni
– Mohsen ZareZardeyni, Commented Dec 21, 2016 at 7:18
No there is not a possibility of two newspaper in same array and I want sum of quantity of 'pickupItems'. like{ {"Newspaper":33},{"Plastic":4} } — Sachin Bhandari
– Sachin Bhandari, Commented Dec 21, 2016 at 7:36

felix · Accepted Answer · 2016-12-21 07:26:10Z

2

try this :

db.collection.aggregate([
   {
      $unwind:"$pickupDetails.pickupItems"
   },
   {
      $group:{
         _id:"$pickupDetails.pickupItems.name",
         quantity:{
            $sum:"$pickupDetails.pickupItems.quantity"
         }
      }
   }
])

output:

{ "_id" : "Plastic", "quantity" : 4 }
{ "_id" : "Newspaper", "quantity" : 33 }

you can add a $match stage at the begining of the pipeline to get results for a particular day

answered Dec 21, 2016 at 7:26

felix

9,3158 gold badges44 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sachin Bhandari Over a year ago

Thanks felix ! It solved my problem. btw, what is the use of $unwind

felix Over a year ago

@SachinBhandari $unwind deconstructs an array field from the input documents to output a document for each element, so you have a document for each item in the array

Collectives™ on Stack Overflow

Aggregate based on Array value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related