# Date range with now-20d not working on nested fields (long post)
**URL:** https://discuss.elastic.co/t/date-range-with-now-20d-not-working-on-nested-fields-long-post/219700
**Category:** Elasticsearch
**Created:** [February 18, 2020, 4:40am UTC](https://discuss.elastic.co/t/date-range-with-now-20d-not-working-on-nested-fields-long-post/219700 "2020-02-18T04:40:08Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@GX1](https://discuss.elastic.co/u/GX1)
#### Post date: [February 18, 2020, 4:40am UTC](https://discuss.elastic.co/t/date-range-with-now-20d-not-working-on-nested-fields-long-post/219700/1 "2020-02-18T04:40:08Z")
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
1. Elasticsearch version
2. index mapping
3. document that I expect to match
4. query that does not match nested level field
5. explain of the non matching query
6. query that will match top level field
> 1. Elasticsearch version
> curl -X GET -H 'Content-Type: application/json' '[http://localhost:9200/?pretty](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"
}
```
> 1. index mapping
> curl -X GET -H 'Content-Type: application/json' '[http://localhost:9200/clients/\_mapping?pretty](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
}
}
}
}
}
}
}
```
> 1. document that I expect to match
> curl -X GET -H 'Content-Type: application/json' '[http://localhost:9200/clients/\_search?pretty](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"
}
}
]
}
}
```
> 1. query that does not match nested level field
> curl -X GET -H 'Content-Type: application/json' '[http://localhost:9200/clients/\_search?pretty](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" : []
}
}
```
> 1. explain of the non matching query
> curl -X GET -H 'Content-Type: application/json' '[http://localhost:9200/clients/\_explain/707577?pretty](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" : []
}
]
}
]
}
}
```
> 1. query that will match top level field
> curl -X GET -H 'Content-Type: application/json' '[http://localhost:9200/clients/\_search?pretty](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"
}
}
]
}
}
```
---
### Author:  [@GX1](https://discuss.elastic.co/u/GX1)
#### Post date: [February 20, 2020, 5:21am UTC](https://discuss.elastic.co/t/date-range-with-now-20d-not-working-on-nested-fields-long-post/219700/2 "2020-02-20T05:21:44Z")
I tried on elasticsearch version 7.5.0 and the results are the same, this means I am doing something wrong in my query, can anyone explain the fault here?
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 19, 2020, 5:21am UTC](https://discuss.elastic.co/t/date-range-with-now-20d-not-working-on-nested-fields-long-post/219700/3 "2020-03-19T05:21:45Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.