Getting a dictionary inside a list by key in mongo

Question

I'm using mongoDB (mongoHQ) in my Flask app (mongoengine). I have a Document that looks like this:

{items: [{id: 1}, {id: 2}, {id: 3}]}

Is there a way to to reach, for example, the dict with id: 1 in a single query?

Currently I'm looping through the items list with a next() statement and I was hoping for a wiser solution. Thanks

Answer 1

In a DB query or python statement?

Using python:

In [21]: d = {"items": [{"id": 1}, {"id": 2}, {"id": 3}]}

In [22]: [i for i in d["items"] if i["id"] == 1]
Out[22]: [{'id': 1}]

(Add [0] to the end of the list comp to just get the dictionary)

Could also use a generator:

In [23]: (i for i in d["items"] if i["id"] == 1)
Out[23]: <generator object <genexpr> at 0x052A1D50>

In [24]: _.next()
Out[24]: {'id': 1}

I'd personally do it the above way, as we're working on a returned record from the DB then.

If you want to extract the field directly (Assuming we only want the first returned document that has {"id":1} in it):

In [35]: class Doc(Document):
    ...:     items = ListField(DictField())
    ...:     

In [36]: newdoc = Doc(items=[{"id":1},{"id":2},{"id":3}])

In [37]: newdoc.save()
Out[37]: <Doc: Doc object>

In [38]: for document in [d for d in Doc.objects(items__id=1).first().items if d["id"]==1]:
    ...:     print document
    ...:     
{u'id': 1}

Extracting just the dictionary that matches is harder, as the field is actually a list of dictionaries. You're not saving any speed by modifying the query to only return the part of the field that matches, so instead return the full field and from python just grab the bit you want. Same speed, and easier to maintain.

Answer 2

I'm not familiar with MongoEngine, but the $ projection operator can filter an array to show only the matched element. In the Mongo shell:

> db.foo.insert({"items": [{"id": 1}, {"id": 2}, {"id": 3}]})
> db.foo.find({'items.id': 1}, {'items.$': true})
{ "_id" : ObjectId("51ce29b68b178484ff2a01ed"), "items" : [  {  "id" : 1 } ] }

More info: http://docs.mongodb.org/manual/reference/projection/positional/

It seems in MongoEngine you do:

Foo.objects(__raw__={'items.id': 1}).only('items.$')

More: http://mongoengine-odm.readthedocs.org/en/latest/guide/querying.html#raw-queries