MongoDB nested array query

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

The [documentation][3] 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!

[1]: https://stackoverflow.com/questions/5250652/query-a-nested-array-in-mongodb "another" [2]: https://groups.google.com/forum/#!searchin/mongodb-user/advertised/mongodb-user/SetYxmdvjTE/NiKGhu6cCKsJ "question" [3]: http://docs.mongodb.org/manual/reference/operator/in/#op._S_in "documentation"

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.

Use nested elemMatch to search nested levels within arrays.

Details https://stackoverflow.com/questions/12629692

Short answer: $in is for a single-value field and $all is for arrays.

First, db.nested.findOne({'level1.level2.0': 'item00'}) doesn't work because level1.level2.0 holds an array and you are trying to compare it with a single value.

Now, db.nested.findOne({'level1.level2.0': {'$in': ['item00']}}) doesn't work either because of a similar reason. $in is for comparing a field with a single value (you have an array) with several values in an array (specified in the query). $in is saying: give me the docs that have this field which value is included in this array.

$all is working because it is saying: give me the docs that have this field with several values and all the values of this array (in the query) are included in that field. (edited)

Might be hard to get but look at what the documentation says for each:

> $all selects the documents where the field holds an array and contains all elements (e.g. <value>, <value1>, etc.) in the array.

> $in selects the documents where the field value equals any value in the specified array (e.g. <value1>, <value2>, etc.)

Hope it helps