MongoDB aggregation, find number of distinct values in documents' arrays

Question

Reading the docs, I see you can get the number of elements in document arrays. For example given the following documents:

{ "_id" : 1, "item" : "ABC1", "description" : "product 1", colors: [ "blue", "black", "red" ] }
{ "_id" : 2, "item" : "ABC2", "description" : "product 2", colors: [ "purple" ] }
{ "_id" : 3, "item" : "XYZ1", "description" : "product 3", colors: [ ] }

and the following query:

db.inventory.aggregate([{$project: {item: 1, numberOfColors: { $size: "$colors" }}}])

We would get the number of elements in each document's colors array:

{ "_id" : 1, "item" : "ABC1", "numberOfColors" : 3 }
{ "_id" : 2, "item" : "ABC2", "numberOfColors" : 1 }
{ "_id" : 3, "item" : "XYZ1", "numberOfColors" : 0 }

I've not been able to figure out if and how you could sum up all the colors in all the documents directly from a query, ie:

{ "totalColors": 4 }

Ivan Manzhos · Accepted Answer · 2017-07-23 20:39:28Z

2

You can use the following query to get the count of all colors in all docs:

db.inventory.aggregate([ 
  { $unwind: '$colors' } , // expands nested array so we have one doc per each array value 
  { $group: {_id: null, allColors: {$addToSet: "$colors"} } }  , // find all colors
  { $project: { totalColors: {$size: "$allColors"}}} // find count of all colors
 ])

answered Jul 23, 2017 at 20:39

Ivan Manzhos

7436 silver badges12 bronze badges

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

Comments

Neil Lunn · Accepted Answer · 2017-07-23 23:27:32Z

Infinitely better is is to simply $sum the $size:

db.inventory.aggregate([
  { "$group": { "_id": null, "totalColors": { "$sum": { "$size": "$colors" } } }
])

If you wanted "distinct in each document" then you would instead:

db.inventory.aggregate([
  { "$group": {
    "_id": null,
    "totalColors": {
      "$sum": {
        "$size": { "$setUnion": [ [], "$colors" ] }
      }
    }
  }}
])

Where $setUnion takes values likes ["purple","blue","purple"] and makes it into ["purple","blue"] as a "set" with "distinct items".

And if you really want "distinct across documents" then don't accumulate the "distinct" into a single document. That causes performance issues and simply does not scale to large data sets, and can possibly break the 16MB BSON Limit. Instead accumulate naturally via the key:

db.inventory.aggregate([
  { "$unwind": "$colors" },
  { "$group": { "_id": "$colors" } },
  { "$group": { "_id": null, "totalColors": { "$sum": 1 } } }
])

Where you only use $unwind because you want "distinct" values from the array as combined with other documents. Generally $unwind should be avoided unless the value contained in the array is being accessed in the "grouping key" _id of $group. Where it is not, it is better to treat arrays using other operators instead, since $unwind creates a "copy" of the whole document per array element.

And of course there was also nothing wrong with simply using .distinct() here, which will return the "distinct" values "as an array", for which you can just test the Array.length() on in code:

var totalSize = db.inventory.distinct("colors").length;

Which for the simple operation you are asking, would be the overall fastest approach for a simple "count of distinct elements". Of course the limitation remains that the result cannot exceed the 16MB BSON limit as a payload. Which is where you defer to .aggregate() instead.

Collectives™ on Stack Overflow

MongoDB aggregation, find number of distinct values in documents' arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related