mongodb multiple $group in single query

Question

I have documents similar to this stored in mongodb :

{
    "_id" : ObjectId("----"),
    "status" : "pending",
    "user" : "huSWFekrPkw_xwtqDueAm4j4tHiuPJf3",
    "type" : "inMemory",
    "question" : "Hello, How are you?",
    "intent" : "Greeting",
    "createdAt" : ISODate("2018-07-24T06:33:59.399Z"),
    "updatedAt" : ISODate("2018-07-24T06:33:59.399Z"),
}
{
    "_id" : ObjectId("----"),
    "status" : "trained",
    "user" : "huSWFekrPkw_xwtqDueAm4j4tHiuPJf3",
    "type" : "inMemory",
    "question" : "Holla",
    "intent" : "Greeting",
    "createdAt" : ISODate("2018-07-25T06:33:59.399Z"),
    "updatedAt" : ISODate("2018-07-25T06:33:59.399Z"),
}
{
    "_id" : ObjectId("----"),
    "status" : "trained",
    "user" : "huSWFekrPkw_xwtqDueAm4j4tHiuPJf3",
    "type" : "inMemory",
    "question" : "want to talk with agent?",
    "intent" : "Agent",
    "createdAt" : ISODate("2018-07-26T06:33:59.399Z"),
    "updatedAt" : ISODate("2018-07-26T06:33:59.399Z"),
}

Aggregation pipelines I want :

$group on intent
$group on status
On the basis of the result of these two groups, I want to filter how many are pending and trained for every particular Intent. Like forGreeting intent, I have 2 pending document and 1 trained document.
Later I also want, How many documents for Greeting intent in today, last 7 days or last month.

So the final document will be something looks like :

{
    "intent" : "Greeting",
    "status_pending" : 1,
    "status_trained" : 2,
    "last_day" : 1,
    "last_seven_day" : 3,
    "last_month" : 7
}
{
    "intent" : "Agent",
    "status_pending" : 1,
    "status_trained" : 1,
    "last_day" : 1,
    "last_seven_day" : 2,
    "last_month" : 3
}

mickl · Accepted Answer · 2018-07-25 05:33:40Z

1

You can use $group and $push all dates and statuses for each intent. Then you can use $filter and $size to count how many items you have for each filter:

db.col.aggregate([
    {
        $group: {
            _id: "$intent",
            statuses: { $push: "$status" },
            dates: { $push: "$createdAt" },
        }
    },
    {
        $project: {
            _id: 0,
            intent: "$_id",
            status_pending: {
                $size: { $filter: { input: "$statuses", as: "s", cond: { $eq: [ "$$s", "pending" ] } } }
            },
            status_trained: {
                $size: { $filter: { input: "$statuses", as: "s", cond: { $eq: [ "$$s", "trained" ] } } }
            },
            last_day: {
                $size: { $filter: { input: "$dates", as: "d", cond: { $gt: [ "$$d", new Date(new Date().setDate(new Date().getDate()-1)) ] } } }
            },
            last_seven_day: {
                $size: { $filter: { input: "$dates", as: "d", cond: { $gt: [ "$$d", new Date(new Date().setDate(new Date().getDate()-7)) ] } } }
            },
            last_month: {
                $size: { $filter: { input: "$dates", as: "d", cond: { $gt: [ "$$d", new Date(new Date().setDate(new Date().getDate()-30)) ] } } }
            },
        }
    }
])

answered Jul 25, 2018 at 5:33

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Himanshu Teotia Over a year ago

thanks for the useful information and correct query.

Shaishab Roy · Accepted Answer · 2018-07-25 05:57:39Z

1

You can try this one also

db.getCollection('collectionName').aggregate([
{$group:{
    _id: "$intent",
    "status_pending": {$sum: {$cond: [{ $eq: ["$status", "pending"]}, 1,0 ]}},
    "status_trained": {$sum: {$cond: [{ $eq: ["$status", "trained"]}, 1,0 ]}},
    "last_day": { $sum: { $cond: [{$gt: [ "$createdAt", new Date(new Date().setDate(new Date().getDate()-1)) ]},1,0] } },
    "last_seven_day": { $sum: { $cond: [{$gt: [ "$createdAt", new Date(new Date().setDate(new Date().getDate()-7)) ]},1,0] } },
    "last_month": { $sum: { $cond: [{$gt: [ "$createdAt", new Date(new Date().setDate(new Date().getDate()-30)) ]},1,0] } }
  }
 }
])

edited Jul 25, 2018 at 5:57

answered Jul 25, 2018 at 5:51

Shaishab Roy

16.9k7 gold badges56 silver badges69 bronze badges

Comments

Milad Rezazadeh · Accepted Answer · 2023-05-14 19:00:33Z

0

In order to perform $groups in a single pipeline, one possible solution is to use $facet. it is much simpler and faster.

{ 
    $facet: {
        intents: [{ $group: { _id: { insurer: '$intent' }, count: { $sum: 1 }} }, { $sort : { 'count' : -1 } }],
        statuses: [{ $group: { _id: { status: '$status' }, count: { $sum: 1 }} }, { $sort : { 'count' : -1 } }]
    }
}

answered May 14, 2023 at 19:00

Milad Rezazadeh

1,48312 silver badges35 bronze badges

Collectives™ on Stack Overflow

mongodb multiple $group in single query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related