MongoDB complex aggregation

Question

I have documents with the following structure in my database:

{
    "reading_ts": ISODate(...),
    "points": 2.3,
    "user_id": 2
}

I will have more documents like this for each user_id per day...millions of them... I would like to implement the following aggregation:

Get the data for one month
Group the data for each user_id
Group the data for each day (so I will have the data for each day, for each user_id)
Get the max 'points' for each user for each day
Count how many users has the max points under value 10, how many between 10 and 20 and how many over 20

I can do the step 1 with a $match I can do the step 3 using this:

{
    "$group": {
        "_id": {
            "$subtract": [
                "$reading_ts",
                {
                    "$mod": [
                        {
                            "$toLong": "$reading_ts"
                        },
                        (1000 * 60 * 60 * 24)
                    ]
                }
            ]
        }
    }
}

The problem is that I don't know how to merge the steps 2 and 3 for now.

felix · Accepted Answer · 2019-06-27 07:47:56Z

1

You can combine the steps 2,3 and 4 in a single $group stage using $dayOfMonth and $max to get the max 'points' for each user for each day.

Then, you can count the users by buckets using the $bucket operator with boudaries set to [0, 10, 20]:

db.collection.aggregate([
  {
    "$match": {
      "reading_ts": {
        "$gte": ISODate("2019-01-01"),
        "$lte": ISODate("2019-01-30")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "user": "$user_id",
        "day": {
          "$dayOfMonth": "$reading_ts"
        }
      },
      "max": {
        "$max": "$points"
      }
    }
  },
  {
    "$bucket": {
      "groupBy": "$max",
      "boundaries": [
        0,
        10,
        20
      ],
      "default": 20,
      "output": {
        "users": {
          "$sum": 1
        },

      }
    }
  }
])

try it online: mongoplayground.net/p/jzZdz2V7R4-

answered Jun 27, 2019 at 7:47

felix

9,3158 gold badges44 silver badges65 bronze badges

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

1 Comment

tatulea Over a year ago

Thanks! The $dayOfMonth is a good approach, but the problem is that I actually have the $match filter for the latest 30 days, not month. It was my bad in the description :)

Collectives™ on Stack Overflow

MongoDB complex aggregation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related