Find nested objects that are null

I have a nested field called 'category':

PUT my_index
{
  "mappings": {
    "my_type": {
      "properties": {
        "name": {
          "type": "keyword",
          "null_value": "NULL"
        },
        "categories": {
          "type": "nested", 
          "properties": {
            "handle": {
              "type": "keyword",
              "null_value": "NULL"
            },
            "name": {
              "type": "keyword",
              "null_value": "NULL",
              "fields": {
                "text": {
                  "type": "text",
                  "norms": false
                }
              }
            }
          }
        }
      }
    }
  }
}

Data:

PUT my_index/my_type/_bulk?refresh
{"index":{"_id":1}}
{"name":null,"categories":[{"handle":null,"name":"books"}]}
{"index":{"_id":2}}
{"name":[],"categories":[null]}
{"index":{"_id":3}}
{"name":"philip","categories":null}
{"index":{"_id":4}}
{"name":["philip"]}
{"index" : {"_id":5}}
{"name":[null]}
{"index": {"_id": 6}}
{"query":{"term":{"name":"NULL"}}}

Using the 5.X version, I want to get the NULL fields. Following works find:

GET my_index/_search
{
  "query": {
    "nested": {
      "path": "categories",
      "query": {
        "term": {
          "categories.handle": "NULL"
        }
      }
    }
  }
}

But how can I check if a whole nested object is null. The following query does not work:

GET my_index/_search
{
  "query": {
    "nested": {
      "path": "categories",
      "query": {
        "term": {
          "categories": "NULL"
        }
      }
    }
  }
}

How can I make a query that gives me ids 2 and 3 for null categories as result?

You can't: empty arrays, arrays that only contain nulls and non-existing arrays are the same to Elasticsearch. The closest that you can do is to find all documents that do not have non-null non-empty categories:

GET my_index/_search 
{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "exists": {
                "field": "categories"
              }
            }
          }
        }
      ]
    }
  }
}
2 Likes

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.