comparison with double in json not working

[sanikolaev]

mysql> drop table if exists t; create table t(f json); insert into t(id,f) values(1,'{"a":8589934592.1}'); select * from t where f.a < 8589934593.1;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t(f json)
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into t(id,f) values(1,'{"a":8589934592.1}')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from t where f.a < 8589934593.1
--------------

Empty set (0.00 sec)
--- 0 out of 0 results in 0ms ---

Expected: since 8589934593.1 is by 1 greater than 8589934592.1 the doc should be matched.

[sanikolaev]

select * from t where double(f.a) < 8589934593.1
--------------

Empty set (0.00 sec)

doesn't work too.

[Nick-S-2018]

Probably, this behavior is expected since the numbers with floating point which we pass to compare are treated as floats. By default, Elastic produces similar results in this case unless you explicitly set the type of object property as double:

curl -X PUT -H 'Content-Type: application/json' localhost:9200/test -d '{
  "mappings": {
    "properties": {
      "f": {
        "type": "object"
      }
    }
  }
}'

curl -H 'Content-Type: application/json' elastic:9200/test/_doc -d  '{
  "f": {"a": 8589934592.1 }
}'

curl -H 'Content-Type: application/json' elastic:9200/test/_search -d  '{
  "query": {
    "range": {
      "f.a": {
        "lt": 8589934593.1
      }
    }
  } }'

{"took":1,"timed_out":false,"_shards":{"total":1,"successful":1,"skipped":0,"failed":0},"hits":{"total":{"value":0,"relation":"eq"},"max_score":null,"hits":[]}}

[sanikolaev]

unless you explicitly set the type of object property as double

Please provide an example.

[Nick-S-2018]

curl -X PUT -H 'Content-Type: application/json' localhost:9200/test -d '{
  "mappings": {
    "properties": {
      "f": {
        "properties": {"a": {"type":"double"}}
      }
    }
  }
}'

curl -H 'Content-Type: application/json' elastic:9200/test/_doc -d  '{
  "f": {"a": 8589934592.1 }
}'

curl -H 'Content-Type: application/json' elastic:9200/test/_search -d  '{
  "query": {
    "range": {
      "f.a": {
        "lt": 8589934593.1
      }
    }
  } }'

{"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":"test","_id":"XmF_HI0BR3QhqisR62K1","_score":1.0,"_source":{
  "f": {"a": 8589934592.1 }
}}]}}