MongoDB: Query and retrieve objects inside embedded array?

Question

Let's say I have the following document schema in a collection called 'users':

{
    name: 'John',
    items: [ {}, {}, {}, ... ]
}

The 'items' array contains objects in the following format:

{
    item_id: "1234",
    name: "some item"
}

Each user can have multiple items embedded in the 'items' array.

Now, I want to be able to fetch an item by an item_id for a given user.

For example, I want to get the item with id "1234" that belong to the user with name "John".

Can I do this with mongoDB? I'd like to utilize its powerful array indexing, but I'm not sure if you can run queries on embedded arrays and return objects from the array instead of the document that contains it.

I know I can fetch users that have a certain item using {users.items.item_id: "1234"}. But I want to fetch the actual item from the array, not the user.

Alternatively, is there maybe a better way to organize this data so that I can easily get what I want? I'm still fairly new to mongodb.

Thanks for any help or advice you can provide.

Answer 1

The question is old, but the response has changed since the time. With MongoDB >= 2.2, you can do :

db.users.find( { name: "John"}, { items: { $elemMatch: { item_id: "1234" } } })

You will have :

{
   name: "John",
   items: 
   [ 
       {
         item_id: "1234",
         name: "some item"
       }
   ]
}

See Documentation of $elemMatch

Answer 2

There are a couple of things to note about this:

1) I find that the hardest thing for folks learning MongoDB is UN-learning the relational thinking that they're used to. Your data model looks to be the right one.

2) Normally, what you do with MongoDB is return the entire document into the client program, and then search for the portion of the document that you want on the client side using your client programming language.

In your example, you'd fetch the entire 'user' document and then iterate through the 'items[]' array on the client side.

3) If you want to return just the 'items[]' array, you can do so by using the 'Field Selection' syntax. See http://www.mongodb.org/display/DOCS/Querying#Querying-FieldSelection for details. Unfortunately, it will return the entire 'items[]' array, and not just one element of the array.

4) There is an existing Jira ticket to add this functionality: it is https://jira.mongodb.org/browse/SERVER-828 SERVER-828. It looks like it's been added to the latest 2.1 (development) branch: that means it will be available for production use when release 2.2 ships.