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

Question

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!

JohnnyHK · Accepted Answer · 2014-02-23 18:39:18Z

2

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 }}
}})

answered Feb 23, 2014 at 18:39

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

Comments

Ilan Frumer · Accepted Answer · 2014-02-23 15:30:07Z

0

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)

answered Feb 23, 2014 at 15:30

Ilan Frumer

32.4k8 gold badges73 silver badges84 bronze badges

1 Comment

Philipp Jardas Over a year ago

Thanks Ilan, although only 90 % correct. :-) Need to use $elemMatch as @JohnnyHK pointed out.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Use the $type operator:

From the docs:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Use the $type operator:

From the docs:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related