CODEWITHSUNDEEP
mongodb
erkpwejropi
erkpwejropi

Reputation: 401

How to join two Aggregation results in MongoDB?

I have a data set looks as 

{"BrandId":"a","SessionId":100,"Method": "POST"}
{"BrandId":"a","SessionId":200,"Method": "PUT"}
{"BrandId":"a","SessionId":200,"Method": "GET"}
{"BrandId":"b","SessionId":300,"Method": "GET"}

I wrote aggregation count distinct session id by brandid:

 db.collection.aggregate([
     {$group: {
                "_id": {
                    brand: "$BrandId",
                    session: "$SessionId"
                },
                count: {$sum: 1}
        }},
        {$group: {
                _id: "$_id.brand",
               countSession:{$sum:1}
        }}
    ])

The expected result of the query is :

{ "_id" : "a", "countSession" : 2 }
{ "_id" : "b", "countSession" : 1 }

Another query is to count where the Method is POST by brand:

 db.collection.aggregate([
        {$match: {Method:"POST"}},
        {$group: {
            _id: '$BrandId', 
            countPOST:{$sum:1}
       }}
    ])

The expected result:

{ "_id" : "a", "countPOST" : 1 }
{ "_id" : "b", "countSession" : 0 }

And now, I want to combine these two query and get the expected result as following: 

{"BrandId:"a","countSession":2,"countPOST":1}
{"BrandId:"b","countSession":1,"countPOST":0}

I do not how to combine these two result of two aggregation, anyone can help?

Upvotes: 4

Views: 8071

Answers (1)

thegreenogre
thegreenogre

Reputation: 1579

You can use $cond operator as follows.

db.Collection.aggregate(
    {
        '$group': {
            '_id': {'BrandId':'$BrandId','Session': '$SessionId'},
            'countPOST':{
                '$sum':{ 
                    '$cond': [{'$eq':['$Method','POST']},1,0]
                }
            }
        }
    },
    {
        '$group': {
            '_id': '$_id.BrandId',
            'countSession': {'$sum':1},
            'countPOST': {'$sum': '$countPOST'}
        }
    }
)

Ouput:

{
    "result" : [ 
        {
            "_id" : "a",
            "countSession" : 2,
            "countPOST" : 1
        }, 
        {
            "_id" : "b",
            "countSession" : 1,
            "countPOST" : 0
        }
    ],
    "ok" : 1
}

Upvotes: 7

Related Questions