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!

Answer 1

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