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.
To sort the whole array by value, or to sort by array elements that are not documents, identify the input array and specify 1 for an ascending sort or -1 for descending sort in the sortBy parameter.
MongoDB can perform sort operations on a single-field index in ascending or descending order. In compound indexes, the sort order determines whether the index can be sorted. The sort keys must be listed in the same order as defined in the index.
In MongoDb, the aggregation is assisted by several methods and operators that can perform different tasks. Among those operators, the $sort operator helps to sort the documents and return the documents in an organized order. And for group sorting in MongoDB, the $group operator is used with the $sort operator.
... 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With