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

**URL:** https://discuss.elastic.co/t/how-to-search-documents-with-empty-nested-array-using-sql/357145
**Category:** Elasticsearch
**Created:** [April 10, 2024, 12:41pm UTC](https://discuss.elastic.co/t/how-to-search-documents-with-empty-nested-array-using-sql/357145 "2024-04-10T12:41:42Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![vinitp](https://avatars.discourse-cdn.com/v4/letter/v/46a35a/32.png) [@vinitp](https://discuss.elastic.co/u/vinitp)
#### Post date: [April 10, 2024, 12:41pm UTC](https://discuss.elastic.co/t/how-to-search-documents-with-empty-nested-array-using-sql/357145/1 "2024-04-10T12:41:43Z")

</div>

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

---

<div class="post-metadata">

### Author: ![Alex\_Salgado-Elastic](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alex_salgado-elastic/32/103081_2.png) [@Alex\_Salgado-Elastic](https://discuss.elastic.co/u/Alex_Salgado-Elastic)
#### Post date: [April 11, 2024, 12:01pm UTC](https://discuss.elastic.co/t/how-to-search-documents-with-empty-nested-array-using-sql/357145/2 "2024-04-11T12:01:32Z")

</div>

You could try something like:

```auto
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.

---

<div class="post-metadata">

### Author: ![vinitp](https://avatars.discourse-cdn.com/v4/letter/v/46a35a/32.png) [@vinitp](https://discuss.elastic.co/u/vinitp)
#### Post date: [April 11, 2024, 12:58pm UTC](https://discuss.elastic.co/t/how-to-search-documents-with-empty-nested-array-using-sql/357145/3 "2024-04-11T12:58:01Z")

</div>

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"**