In MongoDB, how can I sort documents based on a property in an inline object?

Question

In my product collection I can find all products released in the GB region:

> db.products.find({'release.region':'GB'}).pretty(); { "_id" : "foo", "release" : [ { "region" : "GB", "date" : ISODate("2012-03-01T00:00:00Z") }, { "region" : "US", "date" : ISODate("2012-09-01T00:00:00Z") } ] } { "_id" : "bar", "release" : [ { "region" : "FR", "date" : ISODate("2010-07-01T00:00:00Z") }, { "region" : "GB", "date" : ISODate("2012-05-01T00:00:00Z") } ] } { "_id" : "baz", "release" : [ { "region" : "GB", "date" : ISODate("2011-05-01T00:00:00Z") }, { "region" : "NZ", "date" : ISODate("2012-02-01T00:00:00Z") } ] } 

How can I sort the results in ascending order of date using the release date of GB? (for example, the order should be baz, foo, bar)

Note that I cannot perform client side sorting.

Alternatively, how can I better organize the data to make this possible.

Edit: I changed the FR release date for "bar" to illustrate that the vivek solution is wrong.

Answer 1

Since you do not need release elements other than those from the "GB" area, you can do this with aggregate as follows:

 db.products.aggregate( // Filter the docs to just those containing the 'GB' region { $match: {'release.region': 'GB'}}, // Duplicate the docs, one per release element { $unwind: '$release'}, // Filter the resulting docs to just include the ones from the 'GB' region { $match: {'release.region': 'GB'}}, // Sort by release date { $sort: {'release.date': 1}}) 

output:

 { "result": [ { "_id": "baz", "release": { "region": "GB", "date": ISODate("20110501T00:00:00Z") } }, { "_id": "foo", "release": { "region": "GB", "date": ISODate("20120301T00:00:00Z") } }, { "_id": "bar", "release": { "region": "GB", "date": ISODate("20120501T00:00:00Z") } } ], "ok": 1 }