Question about Elasticsearch query with nested and bool

[RafaelAdao (Rafael Adão)]

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:

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):

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

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

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

3. Execute the Query

Here is the query I am using:

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!

[Alex_Salgado-Elastic (Alex Salgado)]

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:

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