Mongo query aggregate with a conditional group and another field

Question

I followed this post by @Blakes-Seven Mongodb aggregate $group for multiple date ranges

But I need to add an additional group by field "$User.Account" and I keep getting an error. When I take that out it works fine.

What I'm trying to do and I'm pretty sure the below won't do it is find the top N users within each of the date ranges...

{
    "message" : "the group aggregate field 'User' must be defined as an expression inside an object",
    "ok" : 0,
    "code" : 15951,
    "name" : "MongoError"
}

Any help would be greatly appreciated. I'm missing something...

>     // work out dates somehow var today = new Date(),
>     oneDay = ( 1000 * 60 * 60 * 24 ),
>     ninetyDays = new Date( today.valueOf() - ( 90 * oneDay ) ),
>     sixtyDays = new Date( today.valueOf() - ( 60 * oneDay ) ),
>     thirtyDays = new Date( today.valueOf() - ( 30 * oneDay ) );
> 
> db.logs.aggregate([
>     { "$match": {
>         "DateTime": { "$gte": ninetyDays },
>         "Orgname": /Inc/
>     }},
>     { "$group": {
>         "_id": {
>             "$cond": [
>                 { "$lt": [ "$DateTime", sixtyDays ] },
>                 "61-90",
>                 { "$cond": [
>                     { "$lt": [ "$DateTime", thirtyDays ] },
>                     "31-60",
>                     "01-30"
>                 ]}
>             ]
>         },
>         "User": "$User.Account",
>         "count": { "$sum": 1 },
>     }},
>     {    $sort: {"count": -1}
>     },
>     { $limit: 25} ])

Sample output
01-30  usera 45
01-30  userc 34
01-30  userf 28
01-30  userq 13
… 20 more rows...
01-30  usery 4
31-60  userb 55
… 23 more rows
31-60  userk 3
61-90  userm 78
61-90  userf 45
... 22 more rows...

61-90  usery 22

Ashh · Accepted Answer · 2019-01-03 08:15:07Z

1

You can follow below syntax to add another field in $group expression

db.logs.aggregate([
  { "$match": {
    "DateTime": { "$gte": ninetyDays },
    "Orgname": /Inc/
  }},
  { "$group": {
    "_id": {
      "User": "$User.Account",
      "date": {
        "$cond": [
          { "$lt": [ "$DateTime", sixtyDays ] },
          "61-90",
          { "$cond": [
            { "$lt": [ "$DateTime", thirtyDays ] },
            "31-60",
            "01-30"
          ]}
        ]
      }
    },
    "count": { "$sum": 1 },
  }},
  { "$sort": { "count": -1 }},
  { "$limit": 25}
])

answered Jan 3, 2019 at 8:15

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

7 Comments

Chris Over a year ago

Thanks for getting back to me! I tried that and although I don't get the error any longer, the end result isn't what I wanted. I want to get the top 25 results within each day's bucket. So I should end up with 75 rows and not just 25. I'm not sure I can do this in one run.

Ashh Over a year ago

Ok then please explain what do you need. And it would be better if you show some sample collection and the output

Chris Over a year ago

I didn't see the subtle change you made by making the conditional section into a group! That was a game changer. So how do I sort and limit within one group at a time? Thanks so much for helping!

Ashh Over a year ago

So above query is now working? So how do I sort and limit within one group at a time? Didn't get this

Chris Over a year ago

It's working better but I can't seem to get 75 rows, 25 in each date category. I only get 25 rows total the top 25 regardless of date category.

|

Muhammad Azam · Accepted Answer · 2023-12-16 21:51:01Z

0

Hope this example will helpful

db.collection.aggregate([
  {
    $group: {
      _id: {
        "type": "$type",
        $cond: {
          if: { $in: ["$category", [1, 2]] },
          then: "$category",
          else: "$otherField" // Replace "otherField" with the actual name of your second field## Heading ##
        }
      },
      count: { $sum: 1 }
    }
  }
])

$in can be replaced with $eq $nin and other required constraints

answered Dec 16, 2023 at 21:51

Muhammad Azam

3242 silver badges8 bronze badges

Collectives™ on Stack Overflow

Mongo query aggregate with a conditional group and another field

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related