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.

Answer 1

... 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.

Answer 2

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.