Can I sort by an element within a nested array in Mongo?

Question

Lets say I have a collection in mongodb whose objects have a nested array. I want to sort based on the value of a particular element of the array. Is this possible?

For example (and I just made the example up), if I have a collection of movie types (action, comedy, romance) and examples submitted by users, can I find all objects where a given user submitted sorted by the date of the movie?

For example, I would like to find all types where 'Aaron' submitted an example, sorted by the year of the example 'Aaron' submitted.

Its almost like a need where clause in the sort.

> db.movies.find().pretty();

{
    "_id" : ObjectId("4f2f07c1ec2cb81a269362c6"),
    "type" : "action",
    "examples" : [
        {
            "title" : "Gladiator",
            "year" : 2000,
            "submitter" : "Aaron"
        },
        {
            "title" : "Mission Impossiple",
            "year" : 1996,
            "submitter" : "Bill"
        },
        {
            "title" : "The Terminator",
            "year" : 1984,
            "submitter" : "Jane"
        }
    ]
}
{
    "_id" : ObjectId("4f2f07edaee5d897ea09f511"),
    "type" : "comedy",
    "examples" : [
        {
            "title" : "The Hangover",
            "year" : 2009,
            "submitter" : "Aaron"
        },
        {
            "title" : "Dogma",
            "year" : 1999,
            "submitter" : "Bill"
        },
        {
            "tile" : "Airplane",
            "year" : 1980,
            "submitter" : "Jane"
        }
    ]
}

> db.movies.find({'examples.submitter': 'Aaron'}).sort({'examples.year': 1}).pretty();

{
    "_id" : ObjectId("4f2f07edaee5d897ea09f511"),
    "type" : "comedy",
    "examples" : [
        {
            "title" : "The Hangover",
            "year" : 2009,
            "submitter" : "Aaron"
        },
        {
            "title" : "Dogma",
            "year" : 1999,
            "submitter" : "Bill"
        },
        {
            "tile" : "Airplane",
            "year" : 1980,
            "submitter" : "Jane"
        }
    ]
}
{
    "_id" : ObjectId("4f2f07c1ec2cb81a269362c6"),
    "type" : "action",
    "examples" : [
        {
            "title" : "Gladiator",
            "year" : 2000,
            "submitter" : "Aaron"
        },
        {
            "title" : "Mission Impossiple",
            "year" : 1996,
            "submitter" : "Bill"
        },
        {
            "title" : "The Terminator",
            "year" : 1984,
            "submitter" : "Jane"
        }
    ]
}

Note the documents are returned sorted by the collections year (as expected) -- any way to sort by just those submitted by a given user? for some extra detail I am using the node-native mongo driver for nodejs.

score 5 · Accepted Answer · answered Feb 06 '12 at 05:33

... any way to sort by just those submitted by a given user?

I don't think this is going to work out the way you want it to.

In MongoDB, queries return whole documents. When you do the following query, you are finding all documents where any submitter matches 'Aaron'.

> db.movies.find({'examples.submitter': 'Aaron'})

Note that 'Aaron' could theoretically match twice within the same document, but only return that document once. This complicates the sorting issue.

> db.movies.find({'examples.submitter': 'Aaron'}).sort({'examples.year': 1})

So what do you expect when one document contains two things by 'Aaron' with different years? Remember that we can only sort the documents, we cannot sort the inner array with the query.

The problem here is that you're using "arrays of objects" which complicates the whole process. Your sorting question assumes that we can act on the inner objects and we really cannot.

Please take a look at the new Aggregation Framework which may provide what you're looking for. Note that this is currently a feature in the unstable branch.

I didn't think I would be able to do it but figured it was worth a shot. I am glad I brought it up as the Aggregation Framework you mention seems like it could get me there once ready/stable. Thanks! — Aaron Silverman, Feb 06 '12 at 16:03

score 1 · Answer 2 · answered Nov 06 '14 at 19:50

1

In case anyone else stumbles upon this, I was able to solve a similar problem by creating an index like:

{'examples.submitter': 1, 'examples.year': 1}

If you force your query to use this index using hint("indexname"), your results will come back in order of the index.

answered Nov 06 '14 at 19:50

Austin Fatheree

842
1
7
18

Could you elaborate more on where you put that file/how that works? I'm also struggling with a problem very similar to the one here. – ritmatter Nov 24 '14 at 14:51
1

You need to use .ensureIndex on your collection. You usually do this with a mongo db ide or on the command line. http://docs.mongodb.org/manual/reference/method/db.collection.ensureIndex/ For the hint you can use mongodb http://docs.mongodb.org/manual/reference/operator/meta/hint/ or with mongoose use the hint function on your query. query.hint({ indexA: 1, indexB: -1}) – Austin Fatheree Nov 25 '14 at 16:05

score 0 · Answer 3 · edited May 23 '17 at 12:24

0

For your case, you will have to use eval and do custom sorting on the server side. Take a look here for a specific example on sorting. Your compare function will be a bit more complex, since you will have to pass through "examples" array and filter by submitter.

edited May 23 '17 at 12:24

Community

1
1

answered Feb 06 '12 at 04:45

Aleksandar Vucetic

14,715
9
53
56

Can I sort by an element within a nested array in Mongo?

3 Answers3