I came across a strange scenario and cant understand why I can not match a nested field by date range
basically I want to find documents where field 'data.api_data.listings_favorites.ts' is within the last 20 days, if I do the same query on field 'date_created' it works. I am certain this used to work and has recently broken
below I have several (LARGE) queries and their results to provide
- Elasticsearch version
- index mapping
- document that I expect to match
- query that does not match nested level field
- explain of the non matching query
- query that will match top level field
- Elasticsearch version
curl -X GET -H 'Content-Type: application/json' 'http://localhost:9200/?pretty'
{
"name" : "gxdev-es",
"cluster_name" : "elasticsearch",
"cluster_uuid" : "d6TURlx6SF-zVmziJqWvRQ",
"version" : {
"number" : "7.5.2",
"build_flavor" : "default",
"build_type" : "rpm",
"build_hash" : "8bec50e1e0ad29dad5653712cf3bb580cd1afcdf",
"build_date" : "2020-01-15T12:11:52.313576Z",
"build_snapshot" : false,
"lucene_version" : "8.3.0",
"minimum_wire_compatibility_version" : "6.8.0",
"minimum_index_compatibility_version" : "6.0.0-beta1"
},
"tagline" : "You Know, for Search"
}
- index mapping
curl -X GET -H 'Content-Type: application/json' 'http://localhost:9200/clients/_mapping?pretty'
{
"clients-20200103081929" : {
"mappings" : {
"properties" : {
"account_id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"creator_operator_id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"data" : {
"properties" : {
"api_data" : {
"properties" : {
"listings_favorites" : {
"type" : "nested",
"properties" : {
"data" : {
"type" : "long"
},
"ts" : {
"type" : "date"
}
}
}
}
},
"clients" : {
"properties" : {
"fields" : {
"properties" : {
"account_id" : {
"type" : "long"
},
"address" : {
"properties" : {
"EN" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"company" : {
"properties" : {
"EN" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"email" : {
"type" : "keyword",
"normalizer" : "case_insensitive_keyword"
},
"job_title" : {
"properties" : {
"EN" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"name" : {
"properties" : {
"EN" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"operator_id" : {
"type" : "long"
},
"phone" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"phone_mobile" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"photo" : {
"properties" : {
"id" : {
"type" : "long"
},
"name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"twitter" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"website" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
}
}
},
"date_created" : {
"type" : "date"
},
"date_updated" : {
"type" : "date"
},
"date_updated_with_note" : {
"type" : "date"
},
"due_date" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"gsdata_id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"labels" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"list_description" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"list_name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"notes" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"operator_id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"reference_description" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"reference_name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"section_id" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
}
}
- document that I expect to match
curl -X GET -H 'Content-Type: application/json' 'http://localhost:9200/clients/_search?pretty' -d '{"query":{"term":{"gsdata_id":707577}}}'
{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 11.315201,
"hits" : [
{
"_index" : "clients-20200103081929",
"_type" : "clients",
"_id" : "707577",
"_score" : 11.315201,
"_source" : {
"gsdata_id" : "707577",
"data" : {
"clients" : {
"fields" : {
"source" : "website",
"email" : "test@example.com"
}
},
"api_data" : {
"listings_favorites" : [
{
"data" : "704854",
"ts" : "2020-02-17T07:09:37"
}
]
}
},
"reference_description" : "",
"reference_name" : "",
"list_description" : "test@example.com",
"date_created" : "2020-02-07T05:54:47",
"notes" : [ ],
"due_date" : "",
"labels" : [ ],
"operator_id" : "26",
"account_id" : "2",
"creator_operator_id" : "26",
"security_operator_id" : "",
"section_id" : "clients",
"list_name" : "",
"date_updated" : "2020-02-07T05:54:47",
"date_updated_with_note" : "2020-02-07T05:54:47"
}
}
]
}
}
- query that does not match nested level field
curl -X GET -H 'Content-Type: application/json' 'http://localhost:9200/clients/_search?pretty' -d '{"query":{"range":{"data.api_data.listings_favorites.ts":{"gte":"now-20d"}}}}'
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 0,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
}
}
- explain of the non matching query
curl -X GET -H 'Content-Type: application/json' 'http://localhost:9200/clients/_explain/707577?pretty' -d '{"query":{"range":{"data.api_data.listings_favorites.ts":{"gte":"now-20d"}}}}'
{
"_index" : "clients-20200103081929",
"_type" : "_doc",
"_id" : "707577",
"matched" : false,
"explanation" : {
"value" : 0.0,
"description" : "Failure to meet condition(s) of required/prohibited clause(s)",
"details" : [
{
"value" : 0.0,
"description" : "no match on required clause (data.api_data.listings_favorites.ts:[1580271917406 TO 9223372036854775807])",
"details" : [
{
"value" : 0.0,
"description" : "data.api_data.listings_favorites.ts:[1580271917406 TO 9223372036854775807] doesn't match id 1",
"details" : [ ]
}
]
},
{
"value" : 0.0,
"description" : "match on required clause, product of:",
"details" : [
{
"value" : 0.0,
"description" : "# clause",
"details" : [ ]
},
{
"value" : 1.0,
"description" : "DocValuesFieldExistsQuery [field=_primary_term]",
"details" : [ ]
}
]
}
]
}
}
- query that will match top level field
curl -X GET -H 'Content-Type: application/json' 'http://localhost:9200/clients/_search?pretty' -d '{"query":{"range":{"date_created":{"gte":"now-20d"}}}}'
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 1.0,
"hits" : [
{
"_index" : "clients-20200103081929",
"_type" : "clients",
"_id" : "707577",
"_score" : 1.0,
"_source" : {
"gsdata_id" : "707577",
"data" : {
"clients" : {
"fields" : {
"source" : "website",
"email" : "test@example.com"
}
},
"api_data" : {
"listings_favorites" : [
{
"data" : "704854",
"ts" : "2020-02-17T07:09:37"
}
]
}
},
"reference_description" : "",
"reference_name" : "",
"list_description" : "test@example.com",
"date_created" : "2020-02-07T05:54:47",
"notes" : [ ],
"due_date" : "",
"labels" : [ ],
"operator_id" : "26",
"account_id" : "2",
"creator_operator_id" : "26",
"security_operator_id" : "",
"section_id" : "clients",
"list_name" : "",
"date_updated" : "2020-02-07T05:54:47",
"date_updated_with_note" : "2020-02-07T05:54:47"
}
}
]
}
}