mongod multi nested is not working as expected

Question

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?

Answer 1

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