Query object with max field on MongoDB

Question

I am new to MongoDB and I use Atlas & Charts in order to query and visualize the results. I want to create a graph that shows the max amount of money every day, and indicate the person with the max amount of money.

for example: if my collection contains the following documents:

{"date": "15-12-2020", "name": "alice", "money": 7}
{"date": "15-12-2020", "name": "bob", "money": 9}
{"date": "16-12-2020", "name": "alice", "money": 39}
{"date": "16-12-2020", "name": "bob", "money": 25}

what should be the query I put on query box (on "Charts") in order to create a graph with the following result?

date        |   max_money |     the_person_with_max_money

15-12-2020          9               bob
16-12-2020          39              alice

Answer 1

You have to use an aggregation and I think this should works.

First of all $sort values by money (I'll explain later why).

And then use $group to group values by date.

The query looks like this:

db.collection.aggregate([
  {
    "$sort": { "money": -1 }
  },
  {
    "$group": {
      "_id": "$date",
      "max_money": { "$max": "$money" },
      "the_person_with_max_money": { "$first": "$name" }
    }
  }
])

Example here

How this works? Well, there is a "problem" using $group, is that you can't keep values for the next stage unless you uses an accumulator, so, the best way it seems is to use $first to get the first name.
And this is why is sorted by money descendent, to get the name whose money value is the greatest at first position.

So, sorting we ensure that the first value is what you want.

And then using group to group the documents with the same date and create the fields max_money and the_person_with_max_money.