Mongodb aggregation sort on array primative

Question

Given documents like

{
   ...
   name:'whatever',
   games: [122, 199, 201, 222]
}

db.col.aggregate({$match:{}},
    {$sort:{'games.0': -1}})

doesn't sort ... no errors ... it just doesn't sort on the first array element of the games array.

Although a query with the same syntac .. works fine

col.find({}).sort({'games.0':-1})

if I change the collection so games is an array of objects like

   [ {game1:198}, {game2:201} ...]

then the aggregation works using

{$sort:{'games.game1': -1}})

what am I missing to get this to work with an array of numbers?

I see what you are trying to do here in general. But may I ask, what is the purpose/benefit of ordering the collection results by the first element of the array? It might just shed some light if you explained what the rest of your intended operations are mean to do. BTW. Just doing { "$sort": { "games": -1 } } will implicitly sort on the first element anyway. — user3561036
– user3561036, Commented Jun 10, 2015 at 3:30
Although the 'real' data set is a bit more complicated .. what I'm essentially doing is asking 'what were the top scores for the nth game'. So a 'games.3' will return the high scores for the 4th game at the top of the document list. What I really want to know is why this type of sort (indexed off an array primitive) works for regular queries but doesn't in an aggregation. — Pablo
– Pablo, Commented Jun 10, 2015 at 22:50
I came across your question while answering this question. In 3.6 version all the find and aggregation semantics issues have been corrected to make them consistent. So your aggregation sort should work same as find sort. Maybe little too late for you but I wanted to leave comment. — s7vr
– s7vr, Commented Jul 26, 2018 at 20:22

chridam · Accepted Answer · 2015-06-09 20:56:52Z

0

Try unwinding the array first by applying the $unwind operator on the array, then use $sort on the deconstructed array and finally use $group to get the original documents structure:

db.coll.aggregate([
    {"$unwind": "$games"},
    {"$sort": {"games": 1}},
    {
        "$group": {
            "_id": "$_id",
            "name": {"$first": "$name"},
            "games": {"$push": "$games"}
        }
    }
])

answered Jun 9, 2015 at 20:56

chridam

104k26 gold badges246 silver badges243 bronze badges

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

1 Comment

Pablo Over a year ago

This will scramble the order of the games. The games array must not be changed as it contains the ordered scores for the individual matches.... and the 'real' document is much larger and there are a lot of them .... not sure I would want to increase the memory footprint 4 times by unwinding .... I'll probably just run map/reduce ... though I'm still surprised by not being able to do the 'simple' sort ... thanks for trying.

Syed Kashan Ali · Accepted Answer · 2018-07-26 19:59:19Z

0

Try this:

db.coll.aggregate([
    {"$unwind": "$games"},
    {"$sort": {"games": -1}}
]}

I hope this will work for you as you expected.

answered Jul 26, 2018 at 19:59

Syed Kashan Ali

6634 silver badges8 bronze badges

Comments

Sagar Chaudhary · Accepted Answer · 2019-09-22 11:45:15Z

0

In mongo 3.4 find sort i.e. db.col.find({}).sort({'games.0':-1}) works as expected whereas aggregation sort doesn't.

In mongo 3.6 both find and aggregation sort works as expected.

Jira issue for that: https://jira.mongodb.org/browse/SERVER-19402

I would recommend you to update your mongo version and your aggregation query will work fine.

answered Sep 22, 2019 at 11:45

Sagar Chaudhary

1,40310 silver badges24 bronze badges

Collectives™ on Stack Overflow

Mongodb aggregation sort on array primative

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related