CODEWITHSUNDEEP
mongodbpymongoaggregation-framework
Marcio Cruz
Marcio Cruz

Reputation: 2069

mongodb: order and limit the collection, then order and limit the resulting document nested documents

I have the following data structure on my game collection:

{
    name: game1
    date: 2010-10-10
    media: [{
        id: 1,
        created: 2010-10-10 00:00:59
    }, {
        id: 2,
        created: 2010-10-10 00:00:30
    }]
},
{
    name: game2
    date: 2010-10-09
    media: [{
        id: 1,
        created: 2010-10-09 00:10:40
    }, {
        id: 2,
        created: 2010-10-09 09:01:00
    }]
}

I want to get the game with the highest date, then get the related media with the highest created to get it's id. In the example above, the result would be

{
    name: game1
    date: 2010-10-10
    media: [{
        id: 1,
        created: 2010-10-10 00:00:59
    }]
}

I tried to use the find and find_one, and also aggregation, but I could not figure a way to make this query.

Any suggestions?

Upvotes: 1

Views: 46

Answers (1)

Sede
Sede

Reputation: 61273

You will need to $unwind the media array in order to get the subdocument in that array where created is the highest then you $sort your documents by date and created all in descending order. Use $limit to output n documents which is 1 in our case.

In [26]: import pymongo

In [27]: conn = pymongo.MongoClient()

In [28]: db = conn.test

In [29]: col = db.gamers

In [30]: list(col.aggregate([{"$unwind": "$media"}, {"$sort": {"date": -1, "media.created": -1}}, {"$limit": 1}]))
Out[30]: 
[{'_id': ObjectId('553323ec0acf450bc6b7438c'),
  'date': '2010-10-10',
  'media': {'created': '2010-10-10 00:00:59', 'id': 1},
  'name': 'game1'
}]

Upvotes: 2

Related Questions