How to check objects nested property existence?

Question

I have an object Document with nested Properties (Name, Value) collection.

Now I want to find documents where "Properties.Name" = "SomePropertyName" doesn't exist.

I tried this but it only works if the property exists but has null value:

{"Properties":{"$elemMatch":{"Name":"SomePropertyName", "Value.0":{"$exists":false}}}}

I tried some wild $ne and $exists combinations that should work back in my relation database querying experience but it doesn't help.

Documents example:

[
  {
    "_id": "Document1",
    "Properties": [
      {
        "Name": "SomeName",
        "Value": [
          "value1",
          "value2"
        ]
      },
      {
        "Name": "Property2",
        "Value": [
          "value3"
        ]
      }
    ]
  },
  {
    "_id": "Document2",
    "Properties": [
      {
        "Name": "Property2",
        "Value": [
          "value3"
        ]
      },
      {
        "Name": "Property3",
        "Value": null
      }
    ]
  },
  {
    "_id": "Document3",
    "Properties": [
      {
        "Name": "SomeName",
        "Value": null
      },
      {
        "Name": "Property2",
        "Value": [
          "value3"
        ]
      },
      {
        "Name": "Property3",
        "Value": null
      }
    ]
  }
]

The query should return Document2 and Document3 (querying against "SomeName" property)

How do I query documents where property doesn't exist or has a null value?

Answer 1

I believe this is the query that you want:

db.prop.find({$or: [
... {"Properties.Name":{$ne:"SomeName"}},
... {"Properties":{$elemMatch:{"Name":"SomeName","Value":null}}}
... ] })

This says you want all documents where "SomeName" is not set (i.e. none of the ones that exist are equal to "SomeName") and also all the documents where Name is "SomeName" and at the same time "Value" is null.

I tried it on your example and got Documents 2 and 3 back.