# Question about Elasticsearch query with nested and bool

**URL:** <https://discuss.elastic.co/t/question-about-elasticsearch-query-with-nested-and-bool/367125>\
**Category:** Elastic Search\
**Created:** [September 25, 2024, 6:42pm UTC](https://discuss.elastic.co/t/question-about-elasticsearch-query-with-nested-and-bool/367125 "2024-09-25T18:42:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![RafaelAdao](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rafaeladao/32/115248_2.png) [@RafaelAdao](https://discuss.elastic.co/u/RafaelAdao)\
**Post date:** [September 25, 2024, 6:42pm UTC](https://discuss.elastic.co/t/question-about-elasticsearch-query-with-nested-and-bool/367125/1 "2024-09-25T18:42:37Z")

</div>

Hello, community!

I am trying to create a query to retrieve contacts from an Elasticsearch index with the following logic:

1. Return only the contacts that have orders from the provider "shopify."
2. Ignore those who have a specific item, "the archived snowboard," in their orders.
3. Ignore contacts that don’t have any items in their orders (historical data without `items` indexed).

### Step-by-Step Testing Instructions

#### 1. Create the Index with Mapping

Here’s the mapping I am using:

```json
PUT /orders
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  },
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "order": {
        "type": "nested",
        "properties": {
          "provider": { "type": "keyword" },
          "items": {
            "type": "nested",
            "properties": {
              "title": {
                "type": "keyword"
              }
            }
          }
        }
      }
    }
  }
}

```

#### 2. Index Example Documents

Here are two documents to index:

- **Document 1** (Order without items):

```json
POST /orders/_doc/1
{
  "order": {
    "provider": "shopify"
  }
}

```

- **Document 2** (Order with items and a different title):

```json
POST /orders/_doc/2
{
  "order": {
    "provider": "shopify",
    "items": [
      {
        "title": "another item"
      }
    ]
  }
}

```

#### 3. Execute the Query

Here is the query I am using:

```json
POST /orders/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "order",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "order.provider": "shopify"
                    }
                  },
                  {
                    "bool": {
                      "must": [
                        {
                          "exists": {
                            "field": "order.items"
                          }
                        }
                      ],
                      "must_not": [
                        {
                          "nested": {
                            "path": "order.items",
                            "query": {
                              "term": {
                                "order.items.title": "the archived snowboard"
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

```

### My Intention Is

My intention is for the query to return Document 2 because:

- Document 1 has no items, so it should be ignored.
- Document 2 has items and does not contain "the archived snowboard" in its items, so it should be returned.

I also noticed that if I remove the `exists` clause, the query returns both documents. However, I want to ensure that only contacts with items are included in the results.

But currently, the query is not returning the expected results. Could anyone help me identify what might be wrong or if there’s something I’m overlooking?

Thank you!

---

<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:** [September 25, 2024, 7:07pm UTC](https://discuss.elastic.co/t/question-about-elasticsearch-query-with-nested-and-bool/367125/2 "2024-09-25T19:07:43Z")

</div>

Hi @RafaelAdao ,

Some notes:

1. You can use a `nested` query to specifically check for the existence of `order.items.title`, ensuring that there are valid items within the order and that those items have titles. This improves the accuracy of the query by ensuring that documents without items or with items lacking a title are correctly excluded from the results.

2. Use two separate `nested` queries: first, to check for the existence of items, and second, to ensure that the item "the archived snowboard" is not present. This guarantees that the field checks within the `items` array are correctly performed at each level.

Try this adjustment and let us know:

```auto
POST /orders/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "order",
            "query": {
              "bool": {
                "must": [
                  {
                    "term": {
                      "order.provider": "shopify"
                    }
                  },
                  {
                    "nested": {
                      "path": "order.items",
                      "query": {
                        "exists": {
                          "field": "order.items.title"
                        }
                      }
                    }
                  }
                ],
                "must_not": [
                  {
                    "nested": {
                      "path": "order.items",
                      "query": {
                        "term": {
                          "order.items.title": "the archived snowboard"
                        }
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 23, 2024, 7:07pm UTC](https://discuss.elastic.co/t/question-about-elasticsearch-query-with-nested-and-bool/367125/3 "2024-10-23T19:07:57Z")

</div>

Closed on October 23, 2024, 7:07pm UTC

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