How to search documents with "empty" nested array using SQL

Elastic Stack Elasticsearch
1

Our custom application builds Elasticsearch SQL dynamically based on the criteria provided in the request. The document has a few nested arrays. e.g. "Person" may have an array of nested "addresses". I could build a SQL to find documents based on "city," which is an attribute within "address." e.g., "select * from person where addresses.city = 'New York' ". However, I want to find all documents with no address. Basically, empty "address" array. The document looks like -
{
"firstname": null,
"lastname": null,
"addresses": [
{
"addrssline1": null,
"addressline2": null,
"city": null
},
{
"addrssline1": null,
"addressline2": null,
"city": null
}
]
}

2

You could try something like:

GET /person/_search
{
  "query": {
    "bool": {
      "must_not": {
        "nested": {
          "path": "addresses",
          "query": {
            "exists": {
              "field": "addresses"
            }
          }
        }
      }
    }
  }
}

In this setup, the must_not combined with nested and exists queries will help find documents where the addresses nested field is empty.

3

Thank you, Alex, for taking the time to respond to my question. If I cannot find an elastic SQL solution, I might have to use the Elastic DSL-based solution you provided above. Since my application builds and executes Elastic SQL, I do not know how to combine SQL and DSL in SQL queries. I was looking for something like "select * from my-index where addresses.length = 0" or something like "select * from my-index where size(addresses) = 0"