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!

Gates VP · Accepted Answer · 2011-07-02 15:38:47Z

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 2, 2011 at 15:38

Gates VP

45.3k11 gold badges108 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

FredArters Over a year ago

Oh duhh! Yea that will work. Thank you. Though unless I misunderstood what you were saying, you can return part of a document.. mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields Just not part of an array without using slice. Thanks!

Gates VP Over a year ago

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.

Esteban Over a year ago

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. mongodb.org/display/DOCS/…

Gates VP Over a year ago

$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.

xentek Over a year ago

@GatesVP you can control the order of keys, if your application code enforces an order on it.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related