Sorting on index of array mongodb

Question

I have a collection where i have objects like:

{
    "_id" : ObjectId("5ab212249a639865c58b744e"),
   "levels" : [ 
        {
            "levelId" : 0,
            "siteId" : "5a0ff11dc7bd083ea6a706b1",
            "title" : "Hospital Services"
        }, 
        {
            "levelId" : 1,
            "siteId" : "5a0ff220c7bd083ea6a706d0",
            "title" : "Reference Testing"
        }, 
        {
            "levelId" : 2,
            "siteId" : "5a0ff24fc7bd083ea6a706da",
            "title" : "Des Moines(Reference Testing)"
        }
    ]
}

I want to sort on the title field of 2nd object of levels array e.g. levels.2.title
Currently my mongo query looks like:

db.getCollection('5aaf63a69a639865c58b2ab9').aggregate([
{$sort : {'levels.2.title':1}}
])

But it is not giving desired results.
Please help.

No its not!! I want to perform on the field in a specific object. Please analyse my question again. — Sagar Chaudhary
– Sagar Chaudhary, Commented Jul 24, 2018 at 12:15
Look at the record. The value of title field in 2nd object of levels array is Des Moines(Reference Testing). I want to perform sorting on that. — Sagar Chaudhary
– Sagar Chaudhary, Commented Jul 24, 2018 at 12:28

s7vr · Accepted Answer · 2018-07-26 19:15:40Z

1

You can try below query in 3.6.

db.col.aggregate({$sort:{"levels.2.title":1}});

This aggregation and find semantics are different in 3.4. More on jira here

So

db.col.find().sort({"levels.2.title":1})

works as expected and aggregation sort is not working as expected.

Use below aggregation in 3.4.

Use $arrayElemAt to project the second element in $addFields to keep the computed value as the extra field in the document followed by $sort sort on field.

$project with exclusion to drop the sort field to get expected output.

db.col.aggregate([
 {"$addFields":{ "sort_element":{"$arrayElemAt":["$levels", 2]}}}, 
 {"$sort":{"sort_element.title":-1}},
 {"$project":{"sort_element":0}}
])

Also, You can use $let expression to output the title field directly in $addFields stage.

db.col.aggregate([
 {"$addFields":{ "sort_field":{"$let:{"vars":{"ele":{$arrayElemAt":["$levels", 2]}}, in:"$$ele.title"}}}},      
 {"$sort":{"sort_field":-1}},
 {"$project":{"sort_field":0}}
])

edited Jul 26, 2018 at 19:15

answered Jul 24, 2018 at 12:29

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

4 Comments

Sagar Chaudhary Over a year ago

Is it possible to directly use {$sort : {'levels.2.title':1}} ?

s7vr Over a year ago

I didn't test it now. Last time when I checked it didn't appear to work and can't find anything in the documentation. What happens when you try ?

Sagar Chaudhary Over a year ago

It gives random data, not as per sorting

s7vr Over a year ago

Alright I did quite a few tests. In 3.4 find sort i.e. db.colname.find().sort({'levels.2.title':1}) works as expected where aggregation sort doesn't. In 3.6 both find and aggregation sort works as expected. Found jira issue that back my tests. Updated question too with details

Collectives™ on Stack Overflow

Sorting on index of array mongodb

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related