CODEWITHSUNDEEP
mongodb
Jordane
Jordane

Reputation: 643

How to merge/join mongodb aggregate?

Given this dataset:

db.calls.insert([{
  "agent": 2,
  "isFromOutside": true,
  "duration": 304
}, {
  "agent": 1,
  "isFromOutside": false,
  "duration": 811
}, {
  "agent": 0,
  "isFromOutside": true,
  "duration": 753
}, {
  "agent": 1,
  "isFromOutside": false,
  "duration": 593
}, {
  "agent": 3,
  "isFromOutside": true,
  "duration": 263
}, {
  "agent": 0,
  "isFromOutside": true,
  "duration": 995
}, {
  "agent": 0,
  "isFromOutside": false,
  "duration": 210
}, {
  "agent": 1,
  "isFromOutside": false,
  "duration": 737
}, {
  "agent": 2,
  "isFromOutside": false,
  "duration": 170
}, {
  "agent": 0,
  "isFromOutside": false,
  "duration": 487
}])

I have two aggregate queries that give the total duration for each agent and the count of outgoing calls for each client:

get outGoingCalls table: 

db.calls.aggregate([
  { $match: {  duration :{ $gt: 0 }, isFromOutside: false } },
  { $group: { _id: "$agent", outGoingCalls: { $sum: 1 } } },
  { $sort: { outGoingCalls: -1 } }
])

get totalDuration table:

db.calls.aggregate([
   { $group: { _id: "$agent", totalDuration: { $sum: "$duration" } } },
   { $sort: {totalDuration: -1 } }
])

How to merge/join these tables (or do only one aggregation) to have something like this:

[
  {_id: 0, totalDuration: ..., outGoingCalls: ...},
  {_id: 1, totalDuration: ..., outGoingCalls: ...},
  {_id: 2, totalDuration: ..., outGoingCalls: ...},
  ...
]

Upvotes: 1

Views: 175

Answers (1)

chridam
chridam

Reputation: 103445

Try the following aggregation framework:

db.calls.aggregate([    
    {
        "$group": {
            "_id": "$agent",
            "outGoingCalls": {
                "$sum": {
                    "$cond": [
                        { 
                            "$and": [
                                {"$gt": ["$duration", 0 ]},
                                {"$eq": ["$isFromOutside", false ]}
                            ]
                        },
                        1,
                        0
                    ]
                }
            },
            "totalDuration": { "$sum": "$duration" }
        }
    },
    { 
        "$sort": {
            "totalDuration": -1,
            "outGoingCalls": -1
        } 
    }
])

Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : 0,
            "outGoingCalls" : 2,
            "totalDuration" : 2445
        }, 
        {
            "_id" : 1,
            "outGoingCalls" : 3,
            "totalDuration" : 2141
        }, 
        {
            "_id" : 2,
            "outGoingCalls" : 1,
            "totalDuration" : 474
        }, 
        {
            "_id" : 3,
            "outGoingCalls" : 0,
            "totalDuration" : 263
        }
    ],
    "ok" : 1
}

Upvotes: 1

Related Questions