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 · Accepted Answer · 2020-01-21 09:42:25Z

2

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

edited Jan 21, 2020 at 9:42

answered Feb 26, 2016 at 13:19

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Čamo Over a year ago

filtered keyword is replaced by bool in newer ES versions.

Val Over a year ago

Yes, this answer provides more details about that change: stackoverflow.com/a/40521602/4604579

Collectives™ on Stack Overflow

Combine nested query with filter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related