How to query all data that has null nested field ? Elastic Search v.7-CodePudding

I'm trying to get all records from index that has cities as null.

Source looks like:

  "_index": "potatos_index",
            "_type": "_doc",
            "_id": "1240862",
            "_score": 14.41736,
            "_source": {
                "accountNumber": "1121212",
                "accountType": "Customer",
                "currency": "USD",
                "country": "USA",
                "cities": null,
           }


      "_index": "potatos_index",
                "_type": "_doc",
                "_id": "1240862",
                "_score": 14.41736,
                "_source": {
                    "accountNumber": "1121212",
                    "accountType": "Customer",
                    "currency": "USD",
                    "country": "USA",
                    "cities": [
                        {
                            "id": "1111",
                            "globalId": "1111"
                        }],
               }

So when I try to search only those source that has cities: null I receive different kinds of errors.

must_not exists returns sources that contains non-null cities.

I have tried different kind of script to filter out the data but no result.

_mapping request, cities has type nested.

"cities": {
                    "type": "nested",
                    "properties": {

Please advice

CodePudding user response：

Did you write the exists query in nested manner?

{
    "query": {
     "bool" : {
      "must_not": [
      "nested": {
        "path": "cities",
        "query": {
                "exists": {
                  "field": "cities"
                }
        }
      } ]
    }
   }
}

Based on this discussion