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. Nested arrays will not work since only the "first" or "outer" array position matched is the position returned. — Blakes Seven
– Blakes Seven, Commented Oct 1, 2015 at 14:33
Possible duplicate of Retrieve only the queried element in an object array in MongoDB collection — Blakes Seven
– Blakes Seven, Commented Oct 1, 2015 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
– James Yang, Commented Oct 2, 2015 at 1: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
– Blakes Seven, Commented Oct 2, 2015 at 2:19

JohnnyHK · Accepted Answer · 2015-10-02 14:24:24Z

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 2, 2015 at 14:24

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Yang Over a year ago

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' } } } } ] })

JohnnyHK Over a year ago

@JamesYang I would say so, yes.

Collectives™ on Stack Overflow

query 2-level array in mongodb

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related