Simple range query not working, why?


(Jacob Bogers) #1

i execute this query below, the datas are always formatted in such a way that a simple string comparison str1 > str2 will return true if the actual date of str1 is larger then str2

>  {
>     "query": {
>       "bool": {
>         "must": [{
>           "range": {
>             "createdAt": {
>               "gte": "2019-09-28 12:23:15",
>               "lte": "9999-99-99 99:99:99"
>             }
>           }
>         }]
>       }
>     }
>   }

But when i execute it is still returning for fields of "createdAt" like (only a snippet is shown as it is quite large)

>    "_source": {
>                     "createdAt": "2018-09-28 12:23:15",
>                     "senderId": 24818700,

(Christian Dahlqvist) #2

According to the documentation gte is greater or equal than while gt is strictly greater than. That result therefore looks OK to me.


(Jacob Bogers) #3

the result is not ok, because 2018 is not in the range 2019-xxx and 9999-xx


(Christian Dahlqvist) #4

Ah, now I see the difference in year....

What happens if you change the end date to something that is actually a valid date/timestamp?

What is the mapping for the field?


(Jacob Bogers) #5

these are just strings not date, but it seeems "elastic" is trying to interpret dates from strings automaticly?

the query seem to work but not for all end dates (only the relevant snippet is shown from original)

"range": {
      		"createdAt": {
      		    "gte": "2019-09-28 12:23:15",
      		    "lt": "2099-09-19 13:00:00"
      		  
      	}

-- works correctly gives back nothing

   	"range": {
      		"createdAt": {
      		    "gte": "2019-09-28 12:23:15",
      		    "lt": "2100-09-19 13:00:00"
      		  
      	}

-- doesnt care returns all dates, doesnt even do string comparison


(Jacob Bogers) #6

the field has no mapping the index defintion looks like this, really simple

> {
>   "mappings": {
>     "_doc": {
>       "properties": {
>         "body-headers": {
>           "type": "join",
>           "relations": {
>              "body": [ "normal", "relay/stat" ]
>           },
>           "eager_global_ordinals": false
>         }
>       }
>     }
>   }
> }

(Christian Dahlqvist) #7

Every field has a mapping. Use the get mapping API to retrieve it.


(Jacob Bogers) #8

thanks ok, it looks like this for this field (snippet)

{
"createdAt": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}


(Christian Dahlqvist) #9

OK, so the field you are querying on is an analysed text field. Can you instead try the keyword version createdAt.keyword?


(Jacob Bogers) #10

yes that works as advertised if i use
"createdAt.keyword"

nice,
can i change mapping of an index

{
  "mappings": {
    "_doc": {
      "properties": {
        "createdAt": {
          "type":   "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        }
      }
    }
  }
}

(Christian Dahlqvist) #11

You can not change the mapping of a field in an index, so will have to reindex.


(Jacob Bogers) #12

i am looking for an analog to SQL create table ... as select ...

does it exist?


(Christian Dahlqvist) #13

https://www.elastic.co/guide/en/elasticsearch/reference/6.4/docs-reindex.html

Should work once you have added the correct mapping to your index template.


(system) #14

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.