CODEWITHSUNDEEP
javascriptmongodb
user771071
user771071

Reputation:

Sum of a field and an array in MongoDB aggregate

I'm having a collection with an array an_array and a field a_field. The array can consist of values (the array may be empty) which can also exist in a_field.

I want to create an aggregate function that sums up the number of values in either a_field or an_array.

Edit: e.g. I want to count how many times 1 is used in a_field and an_array, etcetera.

For example if I have some documents that looks like this:

{
  a_field: 1,
  an_array: [ 1, 3, 4 ],
},
{
  a_field: 3,
  an_array: [],
}
{
  a_field: 2,
  an_array: [ 1 ],
}

I'd like to see an output like this:

[ { _id: 1, a_count: 1, b_count: 2 }, { _id: 2, a_count: 1, b_count: 0 }, { _id: 3, a_count: 1, b_count: 1 }, { _id: 4, a_count: 0, b_count: 1 } ]

However I get an output like this:

[ { _id: 1, a_count: 1, b_count: 2 }, { _id: 3, a_count: 1, b_count: 1 }, { _id:4,  a_count: 1, b_count: 1 } ]

Which is incorrect as it's missing _id 2, and _id 4 has too many counts.

The problem I'm facing however is that when an_array is empty, I will not retrieve all counts properly if the array is empty, plus it gets the value of the a_count which doesn't always match up with the actual id (as can be seen in _id 4).

What I have so far is this:

db.getCollection('test').aggregate( [
    { $match: { 
        a_field: { 
            $ne: null 
        } 
    } },

    { $group: { 
        _id: '$a_field', 
        a_count: { 
            $sum: 1 
        }, 
        an_array: { 
            $push: {
                an_array: '$an_array'
            }
        }
     } },

    { $project: {
        an_array: 1,
        a_count: 1
    } },

    { $unwind: '$an_array' },
    { $unwind: '$an_array.an_array' },

    { $group: {
        _id: '$an_array.an_array',
        b_count: {
            $sum: 1
        },
        a_count: {
            $first: '$a_count'
        }
    } }, 
] );

I would really appreciate some input, as I'd love to do it in one single aggregate. At this point this has become more of a personal quest than an actual implementation. Thanks!

Edit: I'm bound to MongoDB 2.6 for now.

Upvotes: 2

Views: 790

Answers (1)

Nick Bull
Nick Bull

Reputation: 9876

Use $size for the array field, which is new to v2.6 you lucky boy!

Also I don't understand why you'd want to try and count the $a_field - there's always going to be one element as it's not an array - so this aggregation just includes what $a_field is as the _id in the result. you know that the count for $a_field will always be 1 as it's not an array: 

db.test.aggregate([{
  $match: {
    a_field: {
      $ne: null
    }
  }
}, {
 $group: {
    _id: '$a_field',
    an_array_size: {
      $size: '$an_array'
    }
  }
}])

Example console output:

> db.test.find({});
{
  "_id": ObjectId("57ada868700ca97222421d8b"),
  "a_field": 1,
  "an_array": [1, 3, 4]
}

> db.test.aggregate([{
  $match: {
    a_field: {
      $ne: null
    }
  }
}, {
 $group: {
    _id: '$a_field',
    an_array_size: {
      $size: '$an_array'
    }
  }
}])
{
  "result": [{
    "_id": 1,
    "an_array_size": 3
  }],
  "ok": 1
}

Upvotes: 1

Related Questions