CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
koengsen
koengsen

Reputation: 35

MongoDb aggregate and group by two fields depending on values

I want to aggregate over a collection where a type is given. If the type is foo I want to group by the field author, if the type is bar I want to group by user.

All this should happen in one query.

Example Data:

{
   "_id": 1,
   "author": {
     "someField": "abc",
  },
   "type": "foo" 
}

{
   "_id": 2,
   "author": {
     "someField": "abc",
  },
   "type": "foo" 
}

{
   "_id": 3,
   "user": {
     "someField": "abc",
  },
   "type": "bar" 
}

This user field is only existing if the type is bar.

So basically something like that... tried to express it with an $or.

function () { 
var results = db.vote.aggregate( [ 
   { $or: [ {

      { $match : { type : "foo" } },
      { $group :    { _id : "$author", sumAuthor : {$sum : 1} } } },

      { { $match : { type : "bar" } },
      { $group : { _id : "$user", sumUser : {$sum : 1} } } 
   } ] }
] );
return results;
}

Does someone have a good solution for this?

Upvotes: 0

Views: 4474

Answers (3)

Neil Lunn
Neil Lunn

Reputation: 151170

What you want here is the $cond operator, which is a ternary operator returning a specific value where the condition is true or false.

db.vote.aggregate([
    { "$group": {
        "_id": null,
        "sumUser": { 
            "$sum": {
                "$cond": [ { "$eq": [ "$type", "user" ] }, 1, 0 ]
            }
        },
        "sumAuhtor": { 
            "$sum": {
                "$cond": [ { "$eq": [ "$type", "auhtor" ] }, 1, 0 ]
            }
        }
    }}
])

This basically tests the "type" of the current document and decides whether to pass either 1 or 0 to the $sum operation.

This also avoids errant grouping should the "user" and "author" fields contain the same values as they do in your example. The end result is a single document with the count of both types.

Upvotes: 0

Wizard
Wizard

Reputation: 4431

I think it can be done by 

db.c.aggregate([{
    $group : {
        _id : {
            $cond : [{
                $eq : [ "$type", "foo"]
            }, "author", "user"]
        },
        sum : {
            $sum : 1
        }
    }
}]);

Upvotes: 3

ebadedude
ebadedude

Reputation: 161

The solution below can be cleaned up a bit...

For "bar" (note: for "foo", you have to change a bit)

    db.vote.aggregate(
        {
            $project:{
                user:{ $ifNull: ["$user", "notbar"]},
                type:1
            }
        },
        {
            $group:{
                _id:{_id:"$user.someField"},
                sumUser:{$sum:1}
            }
        }
    )

Also note: In you final answer, anything that is not of type "bar" will have an _id=null

Upvotes: 0

Related Questions