Mongo Sort Documents by value in array

Question

I have a list of Students. Each one has an array of points they've earned by category in an array named lightTrail. I want the top 5 people with the most behavior points which is shown at index 1 in the lightTrail array. If it is a tie the second sort field is their total points which is shown at index 0.

Here is what I tried:

db.students.aggregate([{
        $project: {
            fname: 1,
            surname: 1,
            lightTrails: 1,
            '_id': 0
        }
    },
    {
        $sort: {
            "lightTrails.1": -1,
            "lightTrails.0": -1
        }
    }, {
        $limit: 5
    }
], {
    allowDiskUse: true
})

This is the result I wanted:

{
    "fname": "Jim",
    surname: "Jones",
    lightTrails: [200, 70, 30, 30, 15, 15, 40]
}, {
    "fname": "Sean",
    surname: "Marx",
    lightTrails: [180, 50, 50, 20, 20, 15, 25]
}, {
    "fname": "Todd",
    surname: "Lull",
    lightTrails: [150, 40, 60, 15, 15, 10, 10]
}, {
    "fname": "Flynn",
    surname: "Moore",
    lightTrails: [100, 40, 25, 15, 5, 10, 5]
}, {
    "fname": "Al",
    surname: "Ryan",
    lightTrails: [80, 20, 20, 10, 10, 10, 10]
}

The result I got was just 5 students no order:

{
  "fname": "Flynn",
    surname: "Moore",
      lightTrails: [100, 40, 25, 15, 5, 10, 5]
}, {
  "fname": "Jim",
    surname: "Jones",
      lightTrails: [200, 70, 30, 30, 15, 15, 40]
}, {
  "fname": "Al",
    surname: "Ryan",
      lightTrails: [80, 20, 20, 10, 10, 10, 10]
}, {
  "fname": "Todd",
    surname: "Lull",
      lightTrails: [150, 40, 60, 15, 15, 10, 10]
}, {
  "fname": "Sean",
    surname: "Marx",
      lightTrails: [180, 50, 50, 20, 20, 15, 25]
}

I found 2 similar questions but one was for an array of documents which didn't work for me and the other wasn't really asking the same question at all.

Datta · Accepted Answer · 2017-12-05 10:13:15Z

5

You can achieve this by find query. No need to use aggregation.

Query:

db.students.find({},{fname:1,surname:1,lightTrails:1,_id:0}).sort({"lightTrails.0":-1,"lightTrails.1":-1}).limit(5)

Result:

{ "fname" : "Jim", "surname" : "Jones", "lightTrails" : [ 200, 70, 30, 30, 15, 15, 40 ] }
{ "fname" : "Sean", "surname" : "Marx", "lightTrails" : [ 180, 50, 50, 20, 20, 15, 25 ] }
{ "fname" : "Todd", "surname" : "Lull", "lightTrails" : [ 150, 40, 60, 15, 15, 10, 10 ] }
{ "fname" : "Flynn", "surname" : "Moore", "lightTrails" : [ 100, 40, 25, 15, 5, 10, 5 ] }
{ "fname" : "Al", "surname" : "Ryan", "lightTrails" : [ 80, 20, 20, 10, 10, 10, 10 ] }

answered Dec 5, 2017 at 10:13

Datta

1116 bronze badges

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

3 Comments

Ari Jae Over a year ago

Thanks, really easy fix. I wanted to upvote your answer but my rep isn't high enough.

Datta Over a year ago

Welcome.. Thats fine dude. Once your reputation increases then pls vote it.

Datta Over a year ago

@AriJae I hope your reputation is increased. Can you please upvote to my answer ?

Collectives™ on Stack Overflow

Mongo Sort Documents by value in array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related