MongoDB query to retrieve one array value by a value in the array

Question

I have a collection of documents that each contain an array of sub documents. Each subdocument has an time value. I am trying to see if I can return a sub document, based on the time in the sub document.

I know that I can retrieve a sub document using $slice, but $slice only give me a specific index or range and offset.

Example time!

Documents are like so....

{ 
    id: 1234, 
    type: 'a', 
    subs: [
        { time: 123001, val: 'a' },
        { time: 123002, val: 'b' },
        { time: 123003, val: 'c' }
    ]
}

If I do a query with find({}, {subs: {$slice: [2,1]}}) I get back something like:

{ id: 1234, type: 'a', subs: [{ time: 123002, val: 'b' }]}

I want to retrieve that record for example based not on the offset, but based on the 123002 time value.

Possible?

go!

score 5 · Accepted Answer · answered Jul 02 '11 at 15:38

5

As you've designed the data this is not possible.

In MongoDB, queries return an entire document. You can filter specific fields, but if the value of a field is an array, it stops there.

When you have "arrays of objects", you either have to $slice, which is not what you want, or you have to model your data differently.

In your case, the following structure will make your query possible:

{ 
    _id: 1234, 
    type: 'a', 
    subs: {
        '123001': { val: 'a' },
        '123002': { val: 'b' },
        '123003': { val: 'c' }
    }
}

Notice how I've changed subs into a JSON object instead of an array. Now you can do the following query and get only the time you're looking for:

find( { _id: 1234 }, { 'subs.123002': 1 } )

The obvious trade-off here is that you will have to change the way you use change the document. You cannot use $push on subs, you cannot query for {'subs.time': 1234}, instead you have to query for {'subs.1234': { $exists:true} }.

answered Jul 02 '11 at 15:38

Gates VP

44,957
11
105
108

Oh duhh! Yea that will work. Thank you. Though unless I misunderstood what you were saying, you can return part of a document.. http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields Just not part of an array without using slice. Thanks! – FredArters Jul 02 '11 at 16:41
If you use your original structure, you could do `'subs.2'` to get the third element in the array. But you generally don't know what the third item really is. So for arrays, it's generally all or nothing. – Gates VP Jul 03 '11 at 08:23
1

Question is old, but maybe this is useful to someone looking for an answer. It is possible to match one document inside an array with $elemMatch. http://www.mongodb.org/display/DOCS/Dot+Notation+%28Reaching+into+Objects%29#DotNotation%28ReachingintoObjects%29-Matchingwith%24elemMatch – Esteban Oct 02 '12 at 10:09
`$elemMatch` is not perfect though. Last time I played with this, order mattered: `{"$elemMatch": {shape: "square", color: "purple"}}` != `{"$elemMatch": {color: "purple", shape: "square"}}`. That's really sub-par because you can't control that order. – Gates VP Oct 02 '12 at 16:54
@GatesVP you can control the order of keys, if your application code enforces an order on it. – xentek Apr 05 '13 at 00:31

MongoDB query to retrieve one array value by a value in the array

1 Answers1

Linked