MongoDB nested array query

Question

I've asked this as a comment on another question, and also posted a question on mongodb-user. No responses so far, so I'm resorting to asking a separate question.

The documentation states:

If the field holds an array, then the $in operator selects the documents whose field holds an array that contains at least one element that matches a value in the specified array (e.g. , , etc.)

I'm using:

mongod --version: db version v2.2.2, pdfile version 4.5 Thu May 30 12:19:12 git version: d1b43b61a5308c4ad0679d34b262c5af9d664267  mongo --version: MongoDB shell version: 2.0.4 

In MongoDB shell:

db.nested.insert({'level1': {'level2': [['item00', 'item01'], ['item10', 'item11']]}}) 

Here's a list of queries that should work according to the documentation, and the results they produce:

Why doesn't this work?

> db.nested.findOne({'level1.level2.0': 'item00'}) null 

Why do I need the $all?

> db.nested.findOne({'level1.level2.0': {'$all': ['item00']}}) {     "_id" : ObjectId("51a7a4c0909dfd8872f52ed7"),     "level1" : {         "level2" : [             [                 "item00",                 "item01"             ],             [                 "item10",                 "item11"             ]         ]     } } 

At least one of the following should work, right?

> db.nested.findOne({'level1.level2.0': {'$in': ['item00']}}) null  > db.nested.findOne({'level1.level2': {'$in': ['item00']}}) null 

Any ideas? We're considering abandoning MongoDB if the query syntax doesn't work as advertised.

Thanks!

Answer 1

After running some queries, I came to the conclusion that $in doesn't work for an array of arrays.

You can use $elemMatch instead and it'll work, but it is frustrating that MongoDB's documentation doesn't warn about it.

I created this document:

{       "_id": "51cb12857124a215940cf2d4",       "level1": [         [           "item00",           "item01"         ],         [           "item10",           "item11"         ]       ],       "items": [         "item20",         "item21"       ] } 

Notice that the field "items" is an array of strings and this query works perfectly:

db.nested.findOne({"items":{"$in":["item20"]} }) 

Now, "level1.0" is also an array of strings, the only difference is that it's inside another array. This query should work but isn't:

db.nested.findOne({"level1.0":{"$in":["item00"]} }) 

The only way to get the result is using $elemMatch:

db.nested.findOne({"level1":{"$elemMatch":{"$in":['item00']}} }) 

So $elemMatch solves the problem, but the real solution is to update MongoDB's documentation to states that $in doesn't work for arrays of arrays. Perhaps you should submit a request to 10gen.

Answer 2

Use nested elemMatch to search nested levels within arrays.

Details Querying an array of arrays in MongoDB