Checking nested property that can be null

Question

I've the following data in my collection:

{ colour: { r: 0, g: 0, b: 0 }},
{ colour: null },

How can I find all the documents that have colour == null or color.r between some values?

I've tried

.find({ where: { $or: [{colour: null}, {"colour.r": {$gt: 0, $lt: 100}}]}})

but of course this gives me cannot read property 'r' of null for null rows.

Answer 1

Use $where only if there is no other way to express your query

db.test.find({$or: [{"colour": null}, {"colour.r": {$gt: 0, $lt: 100}}]})

Answer 2

For sake of completeness:

db.test.find({$nor: [{"colour.r": {$lte: 0}}, {"colour.r": {$gte: 100}}]})

$nor will match all documents that fail the expressions.

Here, you don't have to explicitly test for null as it is neither greater nor lower than any number -- hence, will fail the tests, just like any number in the range (0,100)¹

---

_{¹Exclusive. If you need to find every document in the range [0,100] inclusive, replace $gte (resp. $lte) by $gt (resp. $lt).}

Answer 3

Unless don't need a single query, you could run 2 queries:

1. Find all documents where color == null
2. Find all documents where color != null and color between 0 and 100