CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Dave
Dave

Reputation: 2576

Return sum of array elements and other fields

Given the following example dataset:

[
  {
    _id: 1,
    prices: [1,2,3],
    category: 'stuff'
  },
  {
    _id: 2,
    prices: [4,5,6],
    category: 'stuff'
  },
  {
    _id: 3,
    prices: [7,8,9],
    category: 'misc'
  }
];

How do I get back data that looks like this:

[
  {
    _id: 1,
    prices: 6,
    category: 'stuff'
  },
  {
    _id: 2,
    prices: 15,
    category: 'stuff'
  },
  {
    _id: 3,
    prices: 24,
    category: 'misc'
  }
]

I can get this:

[
  {
    _id: 1,
    prices: 6
  },
  {
    _id: 2,
    prices: 15
  },
  {
    _id: 3,
    prices: 24
  }
]

By using something like this:

[
  { $unwind: '$prices' },
  { $group: { _id: '$_id', prices: { $sum: '$prices' } } },
  { $project: { prices: 1 } }
]

But I can't figure out how to get it to include "category".

Upvotes: 1

Views: 260

Answers (1)

Sede
Sede

Reputation: 61225

The optimal way to do this is MongoDB 3.2 or newer using the $sum accumulator operator in the $project stage.

db.collection.aggregate([
    { "$project": { 
        "price": { "$sum": "$prices" }, 
        "category": "$category" 
    }}
])

which produces:

{ "_id" : 1, "category" : "stuff", "price" : 6 }
{ "_id" : 2, "category" : "stuff", "price" : 15 }
{ "_id" : 3, "category" : "misc", "price" : 24 }

In MongoDB version <= 3.0 you need to use the $first operator.

db.collection.aggregate([  
    { $unwind: '$prices' },   
    { $group: { 
        _id: '$_id', 
        prices: { $sum: '$prices' }, 
        category: { '$first': '$category' } 
    }} 
])

Upvotes: 1

Related Questions