skip to Main Content

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

2

Answers


  1. 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"]}}}
    ])
    
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search