I have some doubts with $in here , I have following type of documents in my db:
{
"a": [
{
"p": [
{
"pid": {
"CC": "A",
"SN": "1",
"KC": "B"
}
}
]
}
]
}
When I search:
This is working as expected:
db.collection.find({"a.p.pid": {"CC": "A", "SN": "1" , "KC": "B" }})
Then for multiple SN, it works in this way as well:
db.collection.find({ 'a.p.pid.CC': 'A', 'a.p.pid.KC': 'B', 'a.p.pid.SN': { "$in": [ "1", "2", "3" ]} })
and:
db.collection.find({"a.p.pid": {$in:[{ "CC": "A","SN": "1","KC": "B" },{ "CC": "A","SN":"2","KC": "B" } ] } } )
and:
db.collection.find({"a.p": { $elemMatch: { "pid.CC": "KR", "pid.KC": "A","pid.SN": { "$in": [ "1", "2", "3" ] } } }})
But , I’m wondering, why this one is not working similar to the first ones, it comes back with an empty result:
db.collection.find({"a.p.pid": { "CC": "A","KC": "B","SN": { "$in": [ "1", "2", "3" ]}}})
example playground:
Playground
Please, advice?
3
I need to find those documents where SN is one of [“1”, “2”, “3”] and CC:A and KC:B
The $elemMatch is the way to go.
so the main question is why this iteration is working as expected in $elemmatch , but not working in the simple object elements comma separated case
I’d wish there was some kind of a formal spec for the query language, but alas, there is nothing like BNF or any meaningful alternative apart from plain English documentation.
Syntax for $elemMatch query is:
{ <field>: { $elemMatch: { <query1>, <query2>, ... } } }
which translates your $elemMatch query to:
<field>: “a.p”<query1>: {“pid.CC”: {$eq: “A”}}<query2>: {“pid.KC”: {$eq: “B”}}<query3>: {“pid.SN”: {“$in”: [“1″,”2″,”3”]}}
where each <queryX> is parsed individually:
https://www.mongodb.com/docs/manual/reference/operator/query/eq/#syntax
{ <field>: { $eq: <value> } }
https://www.mongodb.com/docs/manual/reference/operator/query/in/#syntax
{ field: { $in: [<value1>, <value2>, ... <valueN> ] } }
Now, why the other query doesn’t behave the same. Let me rewrite it with explicit $eq to make it more obvious:
{
"a.p.pid": {
$eq: {
"CC": "A",
"KC": "B",
"SN": {
"$in": [
"1",
"2",
"3"
]
}
}
}
}
The whole thing passed to $eq operator is a <value>, means it’s not parsed as a language construct. It’s data, not a command. In this case the value to compare the field to would be
{
"CC": "A",
"KC": "B",
"SN": {
"$in": [
"1",
"2",
"3"
]
}
}
as a single BSON.
The most tricky part here is order of fields matter in such comparison, which is rarely a desirable and often unexpected behaviour. In most cases you would want to use $elemMatch instead of whole object comparison because {a:1, b:2} is not the same as {b:2, a:1}.
The fact that not all tools respect order of fields after BSON-JSON conversion only makes it more confusing. Take a look at https://mongoplayground.net/p/hgsG39MEMvJ
0