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!

AntonioOtero AntonioOtero · Accepted Answer · 2013-06-26T17:54:51

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.

MongoDB nested array query

3 Answers