query 2-level array in mongodb

Question

in MongoDB, I have many documents in 2-level array as below:

{
_id:1,
"toPerson": [
        [
            {
                "userid": "test1"
            },
            {
                "userid": "test2"
            }
        ],
        [
            {
                "userid": "test10"
            },
            {
                "userid": "test11"
            }
        ]
  ]
}
.....
{
_id:99,
"toPerson": [
        [
            {
                "userid": "test2"
            },
            {
                "userid": "test3"
            }
        ],
        [
            {
                "userid": "test100"
            },
            {
                "userid": "test101"
            }
        ]
  ]
}

Question is how to query all documents that have userid say test2 ?

Have tried:

col.find({'toPerson.userid':'test2'})

it's return nothing. also I have tried using aggregate but found maybe it's not the right direction.

Anyone can help with this?

UPDATE 1 Just read this post

Retrieve only the queried element in an object array in MongoDB collection

but it's different

Structure different: is {field:[ [{ }], [{ }], .... ]}, not { field:[ {}, {} ] }
I want to keep all returned documents structure untouched, $unwind(make toPerson to be 1-level array) or $$PRUNE(remove some fields) will change the structure returned.

UPDATE 2

What I want is to get following result in ONE statement:

col.find({ 'toPerson.0.userid':'test2' }) + col.find({ 'toPerson.1.userid':'test2' }) + ... ...

Is there any precise counterpart statement of above results combined together ?

Well the query you have does work, but if you expect to only return the elements that match from the array then `.aggregate()` is your only direction. Look at the documentation for the [positional `$` operator](http://docs.mongodb.org/manual/reference/operator/projection/positional/). Nested arrays will not work since only the "first" or "outer" array position matched is the position returned. — Blakes Seven, Oct 01 '15 at 14:33
Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — Blakes Seven, Oct 01 '15 at 14:35
@BlakesSeven I'v tried again the query does not work, because it's `{field:[ [{ }], [{ }], .... ]}` structure, not `{ field:[ {}, {} ] }`, `$elemMatch` also don't solve. — James Yang, Oct 02 '15 at 01:57
Yes it does solve it. As stated your query attempt already does match the document and nested arrays do not matter here, as the "dot notation" merely considers the "path" and the wrapping array is ignored. It is already stated that to return just the matched element you need an `.aggregate()` approach as mentioned there with "two" `$unwind` statements. `$redact` cannot be used here. — Blakes Seven, Oct 02 '15 at 02:19
Figure out what? On serveral asks you are yet to specify which of the options presented you are trying to achieve. Can you please read back and be specific about what you really want to do here? Please? — Blakes Seven, Oct 02 '15 at 10:23
using `$unwind` it will return duplicate _id and the 2-level array is flattened, which is not I want; using query `col.find({"toPerson.0.userid":"test2"})` it's worked, but just can find in first 2-level array. — James Yang, Oct 02 '15 at 10:41
You really need to learn how to ask a clear question and more importantly that your detail belongs in your "question" and not in the comments. What are you saying here? Using "test2" as a value in a single document returns one match per case. Using "test1" would return multiple values in the first document. What do you want to see as a result? Please be specific. That is what the space in your question is meant for. [Use it](http://stackoverflow.com/posts/32890148/edit) — Blakes Seven, Oct 02 '15 at 10:56

score 3 · Accepted Answer · answered Oct 02 '15 at 14:24

3

You can query nested arrays like this using two levels of $elemMatch:

db.test.find({toPerson: {$elemMatch: {$elemMatch: {userid: 'test2'}}}})

The outer $elemMatch says match an array element of toPerson where the value passes the inner array $elemMatch test of an element matching {userid: 'test'}.

answered Oct 02 '15 at 14:24

JohnnyHK

305,182
66
621
471

Just tried it's worked! But one more question: If some toPerson field is 1-level array say: `{_id:3, toPerson:[ {userid:'test2'} ]} `, is it the only way to get both 1-level and 2-level documents containing `test2` ? `col.find({ $or:[ {'toPerson.userid':'test2'}, { 'toPerson':{$elemMatch: {$elemMatch:{'userid': 'test2' } } } } ] })` – James Yang Oct 02 '15 at 14:58
@JamesYang I would say so, yes. – JohnnyHK Oct 02 '15 at 15:13

query 2-level array in mongodb

1 Answers1