MongoDB query for null using dot syntax

Question

Im having trouble querying mongodb for null values using the dot syntax of mongo.

Some things in a db:

db.things.insertMany([
   { a: [{ value: 1 }] },
   { a: [{ value: null }] },
   { a: [{ value: 2 }] }
]);

I want to find all of the documents which have the first element in the 'a' array having a null value.

Queries:

db.getCollection('things').count({ "a.0.value": 1 }) => 1 (as expected) db.getCollection('things').count({ "a.0.value": null }) => 3 (I would expect 1 here also)

I'm at a bit of a loss as to why this is returning all the elements for the second query. It only seems to have this behaviour for array indexed results, which also makes it kind of weird. (eg db.getCollection('things').count({ "a": null }) => 0 as expected)

The only thing I can think of is that its basically cancelling out the whole statement when it has the value null but I don't know how to get around this.

MongoDB v3.4.10

Ashh · Accepted Answer · 2019-05-20 06:16:53Z

1

You can use $expr to use aggregation operator and then find the first index using $arrayElemAt which is $equal to null

db.collection.find({ "$expr": { "$eq": [{ "$arrayElemAt": ["$a.value", 0] }, null] } })

MongoPlayground

For the mongo version prior to 3.6

db.collection.aggregate([
  { "$addFields": {
    "match": { "$arrayElemAt": ["$a.value", 0] }
  }},
  { "$match": { "match": null }}
])

MongoPlayground

If you even want to check with .dot syntax then you have to use $type operator to compare with the null values

db.collection.find({ "a.0.value": { "$type": 10 } })

MongoPlayground

edited May 20, 2019 at 6:16

answered May 20, 2019 at 5:37

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

4 Comments

undefined Over a year ago

Hey, I think that would work if I were in a newer mongo, but I'm in 3.4.10. Do you know if theres something similar for that version?

undefined Over a year ago

Actually even using 3.6 I get no results with that query (im using this repl: docs.mongodb.com/v3.6/tutorial/query-for-null-fields)

undefined Over a year ago

Do you happen to know why dot syntax works for other things but not null?

Ashh Over a year ago

Updated my answer again

Collectives™ on Stack Overflow

MongoDB query for null using dot syntax

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related