How do I find documents with an element at a particular position in an array using MongoDB?

Question

I would like to find all documents where element 0 of ingredients is apple. So I want to get document 1 and 3, but not 2. Is such a thing possible natively in Mongo?

The example as it is does not make sense, but my application was too complicated to put up here.

{
    _id => 1
    name => 'best smoothie' 
    ingredients => Array
        (
            [0] => apple
            [1] => raspberry
            [2] => orange
            [3] => banana
        )
}

{
    _id => 2
    name => 'summer smoothie' 
    ingredients => Array
        (
            [0] => lemon
            [1] => mint
            [2] => apple

        )
}

{
    _id => 3
    name => 'yogurt smoothie' 
    ingredients => Array
        (
            [0] => apple
            [1] => blueberry

        )
}

Example borrowed from - Querying array elements with Mongo.

score 4 · Accepted Answer · answered Jun 21 '13 at 12:14

You can use the array positional operator (docs). What's useful is that you can use that same pattern and specify a specific index rather than using the general $ syntax.

Assuming this is your data:

> db.so1.insert({name:"best smoothie", ingredients: ['apple','raspberry','orange','banana']})
> db.so1.insert({name:"summer smoothie", ingredients: ['lemon','mint','apple']})
> db.so1.insert({name:"yogurt smoothie", ingredients: ['apple','blueberry']})

If you want limit the search to only index position 0, just add that to the array property name as shown below:

> db.so1.find({'ingredients.0':'apple'})

Results:

{
        "_id" : ObjectId("51c4425ff227e278e59f5df5"),
        "name" : "best smoothie",
        "ingredients" : [
                "apple",
                "raspberry",
                "orange",
                "banana"
        ]
}
{
        "_id" : ObjectId("51c4428af227e278e59f5df7"),
        "name" : "yogurt smoothie",
        "ingredients" : [
                "apple",
                "blueberry"
        ]
}

score 1 · Answer 2 · answered Jun 21 '13 at 07:32

1

You should use $unwind with $project mongo functions.

`$unwind` - split up array
`$project` - add smth like index for each splitted element

after you could use simple findOne statement.

answered Jun 21 '13 at 07:32

Dmitry Zagorulkin

8,370
4
37
60

how do you add the index on each new documents created by the `$unwind` command ? – Pierre-Louis Gottfrois Jun 21 '13 at 07:40
@Pierre-LouisGottfrois it pretty simple. `10gen` has a great tutorial about creating index fields. http://docs.mongodb.org/manual/tutorial/create-an-auto-incrementing-field/ – Dmitry Zagorulkin Jun 21 '13 at 08:14
You don't need to use aggregation to do this. See [my](http://stackoverflow.com/a/17235010/95190) answer. – WiredPrairie Jun 21 '13 at 12:18

score 0 · Answer 3 · answered Jun 21 '13 at 07:32

I don't see any way to achieve this using simple array. However here is what you could do using an array of hashes:

> db.collections.find()
{ "_id" : ObjectId("51c400d2b9f10d2c26817c5f"), "ingredients" : [ { "value1" : "apple" }, { "value2" : "orange" } ] }
{ "_id" : ObjectId("51c400dbb9f10d2c26817c60"), "ingredients" : [ { "value1" : "mint" }, { "value2" : "apple" } ] }
{ "_id" : ObjectId("51c400e1b9f10d2c26817c61"), "ingredients" : [ { "value1" : "apple" }, { "value2" : "lemon" } ] }

> db.collections.find({ ingredients: { $elemMatch: { value1: 'apple' }}})
{ "_id" : ObjectId("51c400d2b9f10d2c26817c5f"), "ingredients" : [ { "value1" : "apple" }, { "value2" : "orange" } ] }
{ "_id" : ObjectId("51c400e1b9f10d2c26817c61"), "ingredients" : [ { "value1" : "apple" }, { "value2" : "lemon" } ] }

Actually, there is a way that works well in this situation. See my answer. — WiredPrairie, Jun 21 '13 at 15:28

How do I find documents with an element at a particular position in an array using MongoDB?

3 Answers3