Counting the number of mismatches by field MONGODB

Question

I have a collection of entities like:

"_id" : ObjectId("123b1c231c4a460005dc658r"),
"unitId" : "58bd51815744bf06e001b57b",
"name" : "Main",
"shortName" : "Main",
"controlOperator" : "admin"

I have logins ["admin", "noAdmin", "john", "jack"] and I need to count:

admin : 10
noAdmin : 15
john : 0
jack : 14

I try

db
.getCollection('appeals')
.aggregate([
{$match: {$or: [{unitId: "58bd51815744bf06e001b57b", controlOperator: {$in: ["admin", "noAdmin", "john", "jack"]}}, {unitId: "58bd51815744bf06e001b57b", controlOperator: {$nin: ["admin", "noAdmin", "john", "jack"]}}]}},
{$project: {_id: 0}},
{$group: {_id: "$controlOperator", appeals: {$push: "$controlOperator"},
count: {$sum: 1}}}])

But I get only matched value:

admin : 10
noAdmin : 15
jack : 14

Answer 1

Consider this highly condensed version of docs supplied by the OP:

var r = [
    {controlOperator : "admin"},
    {controlOperator : "admin"},
    {controlOperator : "admin"},
    {controlOperator : "buzz"},
    {controlOperator : "buzz"},
    {controlOperator : "steve"}
];

Here is a way to get the desired output:

// admin and buzz will be found.
// dave will not be found and is not in input set; result = 0
// steve is in input set but not in ops targets so will not show up at all
var ops = ['admin','buzz','dave']

c=db.foo.aggregate([
    // Perform all top level filterings here.  At a minimum we filter for
    // controlOperator but add more as needed:                                    
    {$match: {controlOperator: {$in: ops} }}

    // Let initial $group do the heavy lifting of counting                        
    ,{$group: {_id: "$controlOperator", N: {$sum:1}}}

    // OK.  Things that are not found will not make it into $group,               
    // so we have to turn the problem around and iterate over ops                 
    // to see what is NOT there.                                                  
    // Start by turning the output set into a single doc with an                  
    // array so we can exploit array functions:                                   
    ,{$group: {_id: null, M: {$push: {name: "$_id", N:"$N"}}}}

   
    // Rebuild M but from the perspective of the target ops list,                 
    // not the M array.  If we find an op name in the list, we                    
    // keep the result else we invent a new item with N = 0:                      
    ,{$project: {M: {$map: {
        input: ops,
            in: {$let: {
                vars: {ee: {$filter: {input: '$M',
                           as: 'zz',
                           cond: {$eq:['$$zz.name','$$this']}
                             }}
                      },
                in: {$cond: [
                        {$gt:[{$size:'$$ee'},0]}, // IF found                     

                        // THEN take it; only 0 or 1 items thanks to              
                        // $group.  The item is an array so take $first:          
                        {$first:'$$ee'},

                        // ELSE create a zero entry:                              
                        {name:'$$this', N:0}
                     ]}
            }}
        }}
    }}

    // At this point we have all info we need, but if you really want             
    // to break up that M array, do this.  Note that we opt for "good"            
    // key values here, i.e.                                                      
    //   {name: "admin", N:3}  // GOOD                                            
    // vs. the OP target which puts rvals into the key space:                     
    //   {"admin":3}  // Less good                                                
    ,{$unwind: '$M'}
    ,{$project: {_id:false, name: '$M.name', N:'$M.N'}}

]);

OPINION: This seems like a lot of work considering the client knows the full ops list and can do this:

c=db.foo.aggregate([
    {$match: {controlOperator: {$in: ops} }}
    ,{$group: {_id: "$controlOperator", N: {$sum:1}}}
]);

c.forEach(function(doc) {
    var x = ops.indexOf(doc['_id']);
    print(doc['_id'],x);
    if(x != -1) {
        print(doc);
        ops.splice(x,1); // remove from ops list...
    }
});

// What hasn't been removed is zero:
print("the following are zero:", ops);

Finally: there is potentially a cool use of $setUnion here by taking the ops targets, changing them into a "default" set of {op:name, N:0} objects, and then merging the calculated results on top. Those targets with no group will remain as N:0. I cannot get $setUnion to work against sets of objects though, only scalars.

Answer 2

Query1

• if you only have those 4 possible values you can do the group like bellow to include the zero count
• you can replace your group with this group

*alternative you can keep your group, and add the zero count fields if they are missing, or add them on the client with application code

Playmongo

aggregate(
[{"$match": 
   {"$or": 
     [{"$and": 
         [{"unitId": {"$eq": "58bd51815744bf06e001b57b"}},
           {"controlOperator": 
             {"$in": ["admin", "noAdmin", "john", "jack"]}}]},
       {"$and": 
         [{"unitId": {"$eq": "58bd51815744bf06e001b57b"}},
           {"controlOperator": 
             {"$in": ["admin", "noAdmin", "john", "jack"]}}]}]}},
 {"$group": 
   {"_id": null,
    "admin": 
     {"$sum": {"$cond": [{"$eq": ["$controlOperator", "admin"]}, 1, 0]}},
    "noAdmin": 
     {"$sum": {"$cond": [{"$eq": ["$controlOperator", "noAdmin"]}, 1, 0]}},
    "john": 
     {"$sum": {"$cond": [{"$eq": ["$controlOperator", "john"]}, 1, 0]}},
    "jack": 
     {"$sum": {"$cond": [{"$eq": ["$controlOperator", "jack"]}, 1, 0]}}}}])

Query2

• more general, for anysize of operators array
• the first match and group is like your query
• replace root to make it operator:count
• group by null, to merge all documents to 1
• merge with the zero values {"helen": 0, "jack": 0, "john": 0} (you can create this with javascript)
• replace the root with it

Playmongo

// Assume this is the incoming argument:
var targetOps = ["admin", "noAdmin", "john", "jack"];

// After this point, everything is driven dynamically from targetOps.

// From list, setup the default object where count = 0:
var opsObj = {};
targetOps.forEach(function(x) {
    opsObj[x] = 0;
});


aggregate(
[{"$match": {
   "unitId": "58bd51815744bf06e001b57b"},
   "controlOperator": {"$in": targetOps}
 }},
 {"$group": {"_id": "$controlOperator", "count": {"$sum": 1}}},
 {"$replaceRoot": 
   {"newRoot": {"$arrayToObject": [[{"k": "$_id", "v": "$count"}]]}}},
 {"$group": 
   {"_id": null, "controlOperators": {"$mergeObjects": "$$ROOT"}}},
 {"$replaceRoot": 
   {"newRoot": 
     // Important that opsObj comes *first* and the calculated counts object
     // is overlaid on top of it:
     {"$mergeObjects": [opsObj, "$controlOperators"]}}}
])