Counting the number of mismatches by field MONGODB-CodePudding

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

CodePudding user response：

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"}}}}

    // Use $reduce and walk ops -- NOT M -- and see if we can                     
    // find it.  Essentially, we are going to rebuild $M with the                 
    // highly useful {$concatArrays: [ "$$value", [ new item ]} pattern:          
    ,{$project: {M: {$reduce: {
        input: ops,
        initialValue: [],
        in:{$let: {
            // At this scope, $$this is ops (admin, dave, etc.).                  
            // Use $filter to find it (maybe); there can only                     
            // be 1 or none (thanks to the $group):                               
            vars: {
                ee: {$filter: {
                    input: "$M",
                    as: 'zz',
                    cond: {$eq:["$$zz.name", '$$this']}
                }}
            },

            in: {$concatArrays: [ "$$value",
                  [ {name:"$$this",
                     N: {$cond: [
                      {$gt:[{$size:'$$ee'}, 0]}, // IF we found something         

                      // THEN rebuild with the N calculated from group.
                      // Remember our $filter produces an ARRAY of one object
                      // so we need to get just the scalar N from 
                      // the first element:
                      {$first:"$$ee.N"},

                      // ELSE set N = 0                                                
                      0
                  ]}} ] ]
                }
        }}
      }}  
    }}
]);

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.

CodePudding user response：

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

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": "$controlOperator", "count": {"$sum": 1}}},
 {"$replaceRoot": 
   {"newRoot": {"$arrayToObject": [[{"k": "$_id", "v": "$count"}]]}}},
 {"$group": 
   {"_id": null, "controlOperators": {"$mergeObjects": "$$ROOT"}}},
 {"$replaceRoot": 
   {"newRoot": 
     {"$mergeObjects": 
       [{"helen": 0, "jack": 0, "john": 0}, "$controlOperators"]}}}])