CODEWITHSUNDEEP
mongodb
Philipp Jardas
Philipp Jardas

Reputation: 3302

MongoDB: find records where an element in an array has a null value

Consider the following document:

{
    "_id": 1,
    "properties": [
        {
            "key": "foobar",
            "value": null
        },
        {
            "key": "xxx",
            "value": "yyy"
        }
    ]
}

The properties are indexed:

db.collections.ensureIndex('properties')

I want to find all documents where the value for the key foobar is null.

While this query does find all elements where the value has the desired value:

db.collections.find({ properties: { key: 'xxx', value: 'yyy' } })

The following does not return any results:

db.collections.find({ properties: { key: 'foobar', value: null } })

Thanks for your help!

Upvotes: 2

Views: 2357

Answers (2)

JohnnyHK
JohnnyHK

Reputation: 312129

Use the $type operator that Ilan mentioned, but you need to use it with $elemMatch for it to work correctly in this case as otherwise $type won't be evaluated as an operator:

db.collections.find({properties: {
    $elemMatch: { key: 'foobar', value: { $type: 10 }}
}})

Upvotes: 2

Ilan Frumer
Ilan Frumer

Reputation: 32397

Use the $type operator:

db.collections.find({ properties: { key: 'foobar', value: { $type: 10 } } })

From the docs:

The { cancelDate : { $type: 10 } } query matches documents that contains the cancelDate field whose value is null only; i.e. the value of the cancelDate field is of BSON Type Null (i.e. 10)

Upvotes: 0

Related Questions