Elasticsearch: filtering on fields that matched criteria

Question

I'm using elasticsearch 6.8.8. The mapping of my index is quite complex so I'll simplify it for explanation purposes. Let's say I have an index of companies that have employees in their properties:

{
  "mappings": {
    "properties": {
      "id" : {"type" : "integer"},
      "employee" : {
        "properties" : {
          "id" : {"type" : "integer"},
          "name" : {"type" : "text"},
          "age" : {"type" : "integer"}
        }
      }     
    }
  }
}

I want to search for companies that have at least one employee whose age is between 20 and 30 AND whose name is "Smith".

The query I did (see below) only returns companies that have a employee whose age is between 20 and 30 and an other employee whose name is Smith : they might not be the same employee.

GET company/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "employees.age": {
              "gte": 20,
              "lte": 30
            }
          }
        }
      ],
      "filter": {
        "term": {
          "employees.name": "Smith"
        }
      }
    }
  }
}

How can I specify that both 'filters' should apply on the same field?

Thanks a lot!

Answer 1

First, you need to make your employee structure of type nested (you'll need to rebuild your index as you cannot change the type of employee from object to nested):

{
  "mappings": {
    "properties": {
      "id" : {"type" : "integer"},
      "employees" : {
        "type": "nested",                  <--- add this
        "properties" : {
          "id" : {"type" : "integer"},
          "name" : {"type" : "text"},
          "age" : {"type" : "integer"}
        }
      }     
    }
  }
}

When that's done and all your data is reindexed, you can simply leverage a nested query like this:

GET company/_search
{
  "query": {
    "nested": {
      "path": "employees",
      "query": {
        "bool": {
          "filter": [
            {
              "term": {
                "employees.name": "Smith"
              }
            },
            {
              "range": {
                "employees.age": {
                  "gte": 20,
                  "lte": 30
                }
              }
            }
          ]
        }
      }
    }
  }
}