CODEWITHSUNDEEP
arraysmongodbsize
Matthew Adams
Matthew Adams

Reputation: 2197

How do you query a MongoDB document where an array attribute is null or of zero length?

I'd like to be able to query documents where an array attribute is either null or of zero length. I've tried using $or, like

{ things : { $or : [null, { $size : 0 } ] } }

but MongoDB complains about it:

{ "$err" : "invalid operator: $or", "code" : 10068 }

It appears that MongoDB doesn't want to use $and or $or on array attributes. I thought that simply querying for { things : { $size : 0 } } would return documents that had non-null arrays of zero length as well as null arrays, but that doesn't appear to be true.

How can I issue a query to find all documents where an array attribute is null or of zero length?

Upvotes: 1

Views: 1701

Answers (2)

JohnnyHK
JohnnyHK

Reputation: 312149

You can also do this with $in:

db.test.find({things: {$in: [null, []]}})

That will also include docs with things: [null].

Another way is to use $exists on the first element of the things array:

db.test.find({'things.0': {$exists: false}})

That will find the docs where things does not have at least one element. In contrast with the above, it will still include docs with things: null or things: [], but not things: [null].

Upvotes: 4

Philipp
Philipp

Reputation: 69773

When things is null, it is not of type Array. It is of type Null which is its own data-type. $size only matches arrays. Any document where the field is something different than an array will never be matched by it.

Your syntax error is in how you use the $or operator. You don't use it for a field, you use it as a field.

db.collection.find({
   $or: [
       { things: { $size: 0 } },  // 0-elements array
       { things: null }           // field which is null
   ]
});

Upvotes: 2

Related Questions