Combine nested query with filter

Question

I'm using ES 2.1 and have the following mapping:

"startDate": {
  "type": "date", 
  "format": "yyyy-MM-dd HH:mm:ss:SSS||yyyy-MM-dd HH:mm:ss", 
  "index": "not_analyzed", 
  "store": true
},"identities": {
  "type": "nested",
  "properties": {
    "identityatt": { "type": "integer", "index": "not_analyzed", "store": true },
    "identitykey": { "type": "string", "index": "not_analyzed", "store": true },
    "identityval": { "type": "string", "index": "not_analyzed", "store": true },
    "identitytype": { "type": "integer", "index": "not_analyzed", "store": true }
  }
}

The following queries are fine and they return what I expect:

{ "size": 50,
  "query": {
    "filtered": {
      "filter": {
        "range": {
          "startDate": {
            "from": "2016-02-19 11:11:25",
            "to": "2016-02-27 11:11:25",
            "include_lower": true,
            "include_upper": true
          }
}}}}}

this one filters by a time range, and with the next I want to retrieve all with a special identity type

{
  "size": 50,
  "query": {
    "nested": {
      "path": "identities",
      "filter": {
        "term": {
          "identities.identitytype": "2"
        }
}}}}

But I don't seem to get a query combining those two to work.

I tried adding the time range query to the filters within the nested one, wrapping both filters within nested into a bool filter, I also tried with filtered query, but no luck to combine those two either.

Looking at the sample at https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html , it also contains a range query, but the difference is that it's within the nested object, and my startDate is not contained within my nested object.

Any thoughts on how to combine those queries?

EDIT

I also tried what is proposed here: Combined non-Nested and Nested Query in Elasticsearch and get the error "No query registered for [filter]"

{
  "size": 50,
  "query": {
  "bool": {
  "must": [
  {"filter": {
        "range": {
          "startDate": {
            "from": "2016-02-19 11:11:25",
            "to": "2016-02-27 11:11:25",
            "include_lower": true,
            "include_upper": true
          }
        }
      }},
      {"nested": {
      "path": "identities",
      "filter": { "bool": { "must": [{
        "term": {
          "identities.identitytype": "2"
        },
        "range": {
          "startDate": {
            "from": "2016-02-19 11:11:25",
            "to": "2016-02-27 11:11:25",
            "include_lower": true,
            "include_upper": true
          }
        }}]}
      }
    }
      }
  ]
  }}}

Val Val · Accepted Answer · 2016-02-26T13:19:09

The following query should work. You cannot nest the range query inside the nested one, you need to keep it outside but at the same level in the bool/must.

{
  "size": 50,
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "startDate": {
                  "from": "2016-02-19 11:11:25",
                  "to": "2016-02-27 11:11:25",
                  "include_lower": true,
                  "include_upper": true
                }
              }
            },
            {
              "nested": {
                "path": "identities",
                "filter": {
                  "term": {
                    "identities.identitytype": "2"
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

UPDATE:

In latest ES versions, the above query can be rewritten as follows:

{
  "size": 50,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "startDate": {
              "from": "2016-02-19 11:11:25",
              "to": "2016-02-27 11:11:25",
              "include_lower": true,
              "include_upper": true
            }
          }
        },
        {
          "nested": {
            "path": "identities",
            "filter": {
              "term": {
                "identities.identitytype": "2"
              }
            }
          }
        }
      ]
    }
  }
}

Combine nested query with filter

1 Answers