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.

possible duplicate of [MongoDB extract only the selected item in array](http://stackoverflow.com/questions/3985214/mongodb-extract-only-the-selected-item-in-array) — Foreever, May 25 '14 at 18:18
Hello, @Nebs, can you review the accepted answer and change it? Because there is a solution to this problem without needing to two separate collections in newer mongodb versions. — Burak Karakuş, Apr 08 '15 at 08:14

score 34 · Accepted Answer · answered Apr 12 '14 at 12:33

34

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

answered Apr 12 '14 at 12:33

yesnault

890
8
10

2

This is the best answer to the OP's question, I hope he sees and changes it, otherwise many people will look at the accepted answer and think that there is no solution without two collections. – Burak Karakuş Apr 08 '15 at 08:12

score 7 · Answer 2 · answered Jun 22 '12 at 22:11

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.

Sergio Tulentsev · Answer 3 · 2012-06-22T16:22:43.257

If this is an embedded array, then you can't retrieve its elements directly. The retrieved document will have form of a user (root document), although not all fields may be filled (depending on your query).

If you want to retrieve just that element, then you have to store it as a separate document in a separate collection. It will have one additional field, user_id (can be part of _id). Then it's trivial to do what you want.

A sample document might look like this:

{
    _id: {user_id: ObjectId, item_id: "1234"},
    name: "some item"
}

Note that this structure ensures uniqueness of item_id per user (I'm not sure you want this or not).

Thanks for the answer. I wanted to avoid using two collections for this, but it looks like it's the only way. — nebs, Jun 22 '12 at 16:21

MongoDB: Query and retrieve objects inside embedded array?

3 Answers3