Aggregating data in optional arrays in MongoDB

Question

Using the MongoDB aggregation pipeline, is there a way to sum a value in an optional array field?

Suppose this collection:

db.myCollection.insert([
   {_id: 1}, 
   { _id: 2,
     events: [{_id: 201, value: 10}, {_id:202, value:20}]
   }])

I would like to sum the events.value field using the aggregation pipeline, to produce this:

{_id: 1, totalValue: 0},
{_id: 2, totalValue: 30}

I can't use {$unwind: "$events"} because that would eliminate {_id: 1} from the output, so I tried to push the values to an array, and use $cond to create a single [0] if the element is missing:

 db.myCollection.aggregate([
   {$group: {_id:"$_id", values: {$push: "$events.value"}}},
   {$project: {_id:1, values: {$cond: {
     if: {$gt: [{$size: "$values"}, 0]},
     then: "$values",
     else: [[0]]
     }}}}  ])

This creates the following output:

{ "_id" : 2, "values" : [ [ 10, 20 ] ] }
{ "_id" : 1, "values" : [ [ 0 ] ] }

Now I can use $unwind on the values, but I am unable to sum the values.

Using $group with $sum

db.myCollection.aggregate([
   {$group: {_id:"$_id", values: {$push: "$events.value"}}},
   {$project: {_id:1, values: {$cond: {
     if: {$gt: [{$size: "$values"}, 0]},
     then: "$values",
     else: [[0]]
     }}}},
   {$unwind: "$values"},
   {$group: {_id:"$_id", totalValue: {$sum: "$values"}}}  ])

produces this:

{ "_id" : 1, "totalValue" : 0 }
{ "_id" : 2, "totalValue" : 0 }

and using $project with $add produces an error:

db.myCollection.aggregate([
   {$group: {_id:"$_id", values: {$push: "$events.value"}}},
   {$project: {_id:1, values: {$cond: {
     if: {$gt: [{$size: "$values"}, 0]},
     then: "$values",
     else: [[0]]
     }}}},
   {$project: {_id:1, totalValue: {$add: "$values"}}}  ])

results in an exception:

 exception: $add only supports numeric or date types, not Array

Neil Lunn · Accepted Answer · 2014-09-04 01:58:53Z

4

So your documents may or may not contain an array element for what you wish to sum. So before doing any $unwind operation you apply the $ifNull operator with $project. This takes the field to test as an argument, and an alternate value which is returned if the field is not present or evaluates to null, otherwise the value of the field that exists is returned:

db.myCollection.aggregate([
    { "$project": {
        "events": { "$ifNull": [ "$events", [{ "value": 0 }] ] }
    }},
    { "$unwind": "$events" },
    { "$group": {
        "_id": "$_id",
        "totalValue": { "$sum": "$events.value" }
    }}
])

A much simplified form since content is neither removed due to missing an array and is replaced with a default value that works you your intention.

answered Sep 4, 2014 at 1:58

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

Comments

Dan Solovay · Accepted Answer · 2014-08-31 17:33:14Z

0

I was missing an $unwind statement:

After one $unwind:

db.myCollection.aggregate([
{$group: {_id:"$_id", values: {$push: "$events.value"}}},
{$project: {_id:1, values: {$cond: {
  if: {$gt: [{$size: "$values"}, 0]},
  then: "$values",
  else: [[0]]
  }}}},
{$unwind: "$values"} ])

I get this:

{ "_id" : 2, "values" : [ 10, 20 ] }
{ "_id" : 1, "values" : [ 0 ] }

But if I add a second $unwind:

db.myCollection.aggregate([
{$group: {_id:"$_id", values: {$push: "$events.value"}}},
{$project: {_id:1, values: {$cond: {
  if: {$gt: [{$size: "$values"}, 0]},
  then: "$values",
  else: [[0]]
  }}}},
{$unwind: "$values"},
{$unwind: "$values"}])

I get this:

{ "_id" : 2, "values" : 10 }
{ "_id" : 2, "values" : 20 }
{ "_id" : 1, "values" : 0 }

Now I can add the $group clause:

db.myCollection.aggregate([
{$group: {_id:"$_id", values: {$push: "$events.value"}}},
{$project: {_id:1, values: {$cond: {
  if: {$gt: [{$size: "$values"}, 0]},
  then: "$values",
  else: [[0]]
  }}}},
{$unwind: "$values"},
{$unwind: "$values"},
{$group: {_id:"$_id", totalValue: {$sum: "$values"}}}])

producing the desired result:

{ "_id" : 1, "totalValue" : 0 }
{ "_id" : 2, "totalValue" : 30 }

answered Aug 31, 2014 at 17:33

Dan Solovay

3,1643 gold badges29 silver badges57 bronze badges

3 Comments

Dan Solovay Over a year ago

Got this working, but the answer feels too complex. Any simpler solutions out there?

wdberkeley Over a year ago

Store the sum on the document and update it when you update the array.

Dan Solovay Over a year ago

Thanks, unfortunately not an option here, as I querying data created by a third-party application.

Collectives™ on Stack Overflow

Aggregating data in optional arrays in MongoDB

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related