MongoDb - Sort by highest-value element of array field

Question

I have a collection with documents like follows and I want to sort the documents with respect to the highest date value they contain in status array:

[{
  "_id": "1",
  "name": "Tea",
  "status": [
    {
      "state": "a",
      "date": "2019-08-22"
    },
    {
      "state": "b",
      "date": "2019-07-12"
    },
  ],
  "_id": "2",
  "name": "Coffee",
  "status": [
    {
      "state": "c",
      "date": "2019-05-01"
    },
    {
      "state": "b",
      "date": "2019-12-31"
    }
  ]
}]

So, in the example, the values to be used for sorting should be like:

Tea: highest status date 2019-08-22 (state: a)
Coffee: highest status date 2019-12-31 (state: b)

Any ideas?

I fail to see the usefulness of an array here. Create flat documents with an index on name. — Markus W Mahlberg
– Markus W Mahlberg, Commented Jul 12, 2019 at 17:16

Alex Blex · Accepted Answer · 2019-07-12 16:43:46Z

6

db.collection.find().sort({"status.date":-1})

From the docs:

With arrays, a less-than comparison or an ascending sort compares the smallest element of arrays, and a greater-than comparison or a descending sort compares the largest element of the arrays.

edited Jul 12, 2019 at 16:43

answered Jul 12, 2019 at 15:04

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

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

Comments

Ashh · Accepted Answer · 2019-07-12 17:32:27Z

0

To get sorted element with repect to max date inside the array, you need to extract the maximum date from the array which you can easily do with the $max operator.

db.collection.aggregate([
  { "$addFields": {
    "sortDate": { "$max": "$status.date" }
  }},
  { "$sort": { "sortDate": 1 }}
])

edited Jul 12, 2019 at 17:32

answered Jul 12, 2019 at 15:55

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

2 Comments

vahdet Over a year ago

This provides both ascending/descending sort based on max value.

Ashh Over a year ago

If you want the order in descending order with the max date then Alex answer will work for you. Else you can go with this.

Collectives™ on Stack Overflow

MongoDb - Sort by highest-value element of array field

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related