getting all counts returned with in an array

Question

I have the following querys.

//returns total where field callback exist and not empty
db.parts.find({callback:{"$exists" : true, "$ne" : ""}}).count();

//returns total where field callback exist and empty
db.parts.find({callback:{"$exists" : true, "$in" : [""]}}).count();

//returns total where field callback doesn't exist
db.parts.find({callback:{"$exists" : false}}).count();

I would like to construct a query that returns all three counts in an array like the one below.

{ "callbacks" : sum, "emptycallbacks" : sum, "missingcallbacks": sum }

Thanks.

Neil Lunn · Accepted Answer · 2014-07-26 05:42:05Z

1

The aggregation framework is what you are looking for here. You obtain the results mainly from the $cond operator within a $group pipeline:

db.parts.aggregate([
    { "$group": {
        "_id": null,
        "callbacks": {
            "$sum": {
                "$cond": [
                    { "$ne": [{ "$ifNull": ["$callback",""]}, "" ] },
                    1,
                    0
                ]
            }
        },
        "emptycallbacks": {
            "$sum": {
                "$cond": [
                    { "$eq": [ "$callback", "" ] },
                    1,
                    0
                ]
            }
        },
        "missingcallbacks": {
            "$sum": {
                "$cond": [
                    {"$ifNull": [ "$callback", false ] },
                    0,
                    1
                ]
            }
        }
    }}
])

Each is using different comparison operators but mainly $ifNull to test whether the field exists in the document or not. Since $cond is a ternary operator, the second and third arguments are evaluated from where the condition is true or false respectively.

Those values are passed to the $sum operator to add them up. And the null as the grouping key means to add up everything in your collection without "grouping" on a specific field value.

Given the following documents:

{ "callback" : "a" }
{ "callback" : "" }
{ "other" : "a" }
{ "callback" : "" }
{ "something" : "a" }
{ "else": "a" }

The result is:

{ "_id" : null, "callbacks" : 1, "emptycallbacks" : 2, "missingcallbacks" : 3 }

Which shows all conditions are counting correctly.

You will likely need to expand on that logic for your actual documents, but this is the general direction.

answered Jul 26, 2014 at 5:42

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

2 Comments

fpena06 Over a year ago

Thanks Neil! I had something similar going with aggregation framework but using $exist instead and kept getting and error. 'errmsg" : "exception: invalid operator '$exists'",'

Neil Lunn Over a year ago

$exists is a query operator. You can use it in $match stage which is a query, but the intent is to "filter" documents. $ifNull either returns the content of the field where present or the second argument. It is used here to feed the logical evaluations.

Collectives™ on Stack Overflow

getting all counts returned with in an array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related