Why do these queries return ambiguous results?

Working with Data
1

query 1:

db.getCollection('collection_name').find(
{
    "deviceAttr.deviceId": { $exists : true },
    "deviceAttr.deviceId": { "$ne" : null },
    "deviceAttr.deviceId": { "$ne" : "" }
})

return :18065 results

query 2:

db.getCollection('collection_name').find(
{
    "deviceAttr.deviceId":{ $exists:true }
})

return: 1 result

query 3:

db.getCollection('collection_name').find(
{
    "deviceAttr.deviceId" : { "$ne" : null },
    "deviceAttr.deviceId" : { "$ne" : "" }
})

return :18065 results

query 4:

db.getCollection('collection_name').find(
{
    "$and" : [
        { "deviceAttr.deviceId" : { "$ne" : null } },
        { "deviceAttr.deviceId" : { "$ne" : "" } }
    ]
})

return : 1 result

I am confused about the results , it really completely overturn what I’ve known.

2

Welcome to the community @zhao_chao!

The issue with queries 1 and 3 is that you have repeated deviceAttr.deviceId keys within the same object. JavaScript requires keys at the same level to be unique and will only use the last value set. Both of these queries are equivalent to:

“deviceAttr.deviceId” : { “$ne” : “” }

You can confirm this in the mongo shell:

> query = {
  "deviceAttr.deviceId": { $exists : true },
  "deviceAttr.deviceId": { "$ne" : null },
  "deviceAttr.deviceId": { "$ne" : "" }
}
{ "deviceAttr.deviceId" : { "$ne" : "" } }

Your query 4 example avoids the issue of repeated fields using the $and query operator, which is the correct approach for Queries With Multiple Expressions Specifying the Same Field in JavaScript. The result of matching all of the query conditions is a single document, which is expected as the combination of the previous queries.

Regards,
Stennie