mongodb $aggregate empty array and multiple documents

Question

mongodb has below document:

> db.test.find({name:{$in:["abc","abc2"]}})
{ "_id" : 1, "name" : "abc", "scores" : [ ] }
{ "_id" : 2, "name" : "abc2", "scores" : [ 10, 20 ] }

I want get scores array length for each document, how should I do?

Tried below command:

db.test.aggregate({$match:{name:"abc2"}}, {$unwind: "$scores"}, {$group: {_id:null, count:{$sum:1}}} )

Result:

{ "_id" : null, "count" : 2 }

But below command:

db.test.aggregate({$match:{name:"abc"}}, {$unwind: "$scores"}, {$group: {_id:null, count:{$sum:1}}} )

Return Nothing. Question:

How should I get each lenght of scores in 2 or more document in one command?
Why the result of second command return nothing? and how should I check if the array is empty?

Neil Lunn · Accepted Answer · 2015-03-20 21:46:33Z

15

So this is actually a common problem. The result of the $unwind phase in an aggregation pipeline where the array is "empty" is to "remove" to document from the pipeline results.

In order to return a count of "0" for such an an "empty" array then you need to do something like the following.

In MongoDB 2.6 or greater, just use $size:

db.test.aggregate([
    { "$match": { "name": "abc" } },
    { "$group": {
       "_id": null,
       "count": { "$sum": { "$size": "$scores" } }
    }}
])

In earlier versions you need to do this:

db.test.aggregate([
    { "$match": { "name": "abc" } },
    { "$project": {
        "name": 1,
        "scores": {
            "$cond": [
                { "$eq": [ "$scores", [] ] },
                { "$const": [false] },
                "$scores"
            ]
        }
    }},
    { "$unwind": "$scores" },
    { "$group": {
        "_id": null,
        "count": { "$sum": {
            "$cond": [
                "$scores",
                1,
                0
            ]
        }}
    }}
])

The modern operation is simple since $size will just "measure" the array. In the latter case you need to "replace" the array with a single false value when it is empty to avoid $unwind "destroying" this for an "empty" statement.

So replacing with false allows the $cond "trinary" to choose whether to add 1 or 0 to the $sum of the overall statement.

That is how you get the length of "empty arrays".

edited Mar 20, 2015 at 21:46

answered Mar 20, 2015 at 10:04

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

3 Comments

Neil Lunn Over a year ago

@Michael The OP took the trouble of "trying something" So I think that is a nice thing to reward as well.

James Yang Over a year ago

@NeilLunn Very quick & precise answer! Just tried in my project it's worked except a typo: db.test.aggregate({ should be db.test.aggregate([

F.H. Over a year ago

New in version 3.2 of mongodb we have preserveNullAndEmptyArrays which prevents removing items with empty array from the pipeline result. $unwind: { path: '$someArray', preserveNullAndEmptyArrays: true, }

chridam · Accepted Answer · 2016-09-20 12:58:46Z

6

To get the length of scores in 2 or more documents you just need to change the _id value in the $group pipeline which contains the distinct group by key, so in this case you need to group by the document _id.

Your second aggregation returns nothing because the $match query pipeline passed a document which had an empty scores array. To check if the array is empty, your match query should be

{'scores.0': {$exists: true}} or {scores: {$not: {$size: 0}}} Overall, your aggregation should look like this:

db.test.aggregate([
    { "$match": {"scores.0": { "$exists": true } } },
    { "$unwind": "$scores" },
    {
        "$group": {
           "_id": "$_id",
           "count": { "$sum": 1 }
        }
    }
])

edited Sep 20, 2016 at 12:58

answered Mar 20, 2015 at 10:11

chridam

104k26 gold badges246 silver badges243 bronze badges

4 Comments

Neil Lunn Over a year ago

I think the overall point here was to get a 0 count "as well as others". But this does make a valid point to "filtering" 0 length arrays

chridam Over a year ago

You are right, from the way the OP structured the questions I got the impression that he needs to "filter" empty arrays earlier in the aggregation especially in the second question.

Neil Lunn Over a year ago

My "impression" was that they "wanted" the 0 and $unwind "Destroyed" that. Hence the form of the answer I gave. All points are learning points.

James Yang Over a year ago

@NeilLunn, yes it is, the key is $unwind here cannot get 0 length array. But @chridam also gave a good scores.0 solution to get array size, except the 0 length array.

Collectives™ on Stack Overflow

mongodb $aggregate empty array and multiple documents

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related