Watch keynotes and sessions from MongoDB.live, our virtual developer conference.

Why mongodb finds this document ($ne: null for array)

Data:

db.inventory.insertMany([
       { _id: 1, item: null },
       { _id: 2 },
       { _id: 3, item: 3 },
       { _id: 4 items: [1, 2, 3] },
       { _id: 5, items: [] }
    ])

Query 1:

db.inventory.find({ 'item': {$ne: null} })

Result 1:

{ _id: 3, item: 3 }

Query 2:

db.inventory.find({ 'items.0': {$ne: null} })

Result 2:

{ _id: 3, items: [1, 2, 3] },
{ _id: 4, items: [] }

Why mongoDB finds this document: { _id: 4, items: }?
If use $exists then everything is ok. But why does not work with $ne (like https://docs.mongodb.com/manual/tutorial/query-for-null-fields/)

Hi Serg,

I think the behaviour you’re seeing is described in SERVER-27442. It is a known ambiguity if you’re combining null and array notation with equality/inequality.

A quick workaround I can think of is using aggregation to determine the field type (array), and project the first element of the array. Something like:

> db.inventory.aggregate([
  {$match: {items: {$type:'array'}}},
  {$project: {first: {$arrayElemAt: ['$items',0]}, items: '$items'}},
  {$match:{first:null}}
])
{ "_id" : 5, "items" : [ ] }

Unfortunately it’s not an elegant solution, but off the top of my head, this aggregation could work.

Best regards,
Kevin