Unable to get gte and lte from elastic Java API in range query

AKASH_DUBEY1 · December 6, 2020, 3:53pm

Hi ,
I am using RestHighLevelclient to get data from elastic.I am using below query
BoolQueryBuilder boolQuery=QueryBuilders.boolQuery();
		boolQuery.must(QueryBuilders.rangeQuery("ipFromLong").gte(ipLong));
		boolQuery.must(QueryBuilders.rangeQuery("ipToLong").lte(ipLong));

when it is converted it is executed as
{
  "size": 10000,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "ipFromLong": {
              "from": 2888966725,
              "to": null,
              "include_lower": true,
              "include_upper": true,
              "boost": 1
            }
          }
        },
        {
          "range": {
            "ipToLong": {
              "from": null,
              "to": 2888966725,
              "include_lower": true,
              "include_upper": true,
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}
so why no gte and lte in query.I tried multiple ways.still no solution.`Preformatted text`

dadoonet · December 6, 2020, 4:07pm

Welcome!

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.

The generated query looks good to me. Why do you think it is not correct?

BTW why 2 must clauses? I think that you can have only one, no?

AKASH_DUBEY1 · December 6, 2020, 4:23pm

I did that .please reply if you can help me.

dadoonet · December 6, 2020, 9:27pm

I already answered. Please read it again.

AKASH_DUBEY1 · December 7, 2020, 10:48am

the query is not working and if you see there are two fields IpToLong and IPFromLong.
and I what to apply range query on two.if given long is gte than IPFromlong and lte than IptoTolong it's not working if I use them together.

dadoonet · December 7, 2020, 11:36am

Ha right. I misread the query.

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

AKASH_DUBEY1 · December 8, 2020, 5:57am

Sample record
POST  http://localhost:9205/iptoproxy/1
```

{
"ipToLong": 28147177550,
"ipFromLong": 28147177543,
"ipTo": "0:0:0:0:0:ffff:4131:6d83",
"ipFrom": "0:0:0:0:0:ffff:4131:6d83",
"proxyType": "DCH",
"countryCode": "JP",
"countryName": "Japan",
"region": "Tokyo",
"city": "Tokyo",
"isp": "Hurricane Electric LLC",
"domain": "he.net",
"proxyCatagory": "DCH",
"catagoryDesc": "Data Center/Web Hosting/Transit",
"ipType": null,
"ipToProxyIndex": "iptoproxy"
}
```

POST http://localhost:9205/iptoproxy/_search
```
{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "ipFromLong": {
              "from": 2888966725,
              "to": null,
              "include_lower": true,
              "include_upper": false,
              "boost": 1
            }
          }
        },
        {
          "range": {
            "ipToLong": {
              "from": null,
              "to": 2888966725,
              "include_lower": true,
              "include_upper": true,
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}
```
My Use case is to get a record of my long value is greater than or equal to ipFromLong and less than or equal to prolong. If I create a query using Java API I get from and to instead of gte and lte which doesn't work as expected even If try using gte and lte together still it didn't work but If I try Get and let in two different queries they works without any issue. my first concern is why I don't get LTE and GTE even if I use there methods from range queries like gte(ipLong) and lte(iplong) .second why I can't use them in two caluses in same query .

dadoonet · December 8, 2020, 6:43am

You didn't define a mapping?

AKASH_DUBEY1 · December 8, 2020, 8:22am

Hi ,

This is the mapping 
```
{
"iptoproxy": {
"mappings": {
"dynamic_templates": [
{
"free_as_keywords": {
"match": "free_text_*",
"match_mapping_type": "long",
"mapping": {
"copy_to": "free_text_search",
"type": "text"
}
}
}
,
{
"strings_as_keywords": {
"match": "*",
"unmatch": "free_text_*",
"match_mapping_type": "string",
"mapping": {
"copy_to": "free_text_search",
"type": "keyword"
}
}
}
,
{
"integer_as_keywords": {
"match": "*",
"match_mapping_type": "long",
"mapping": {
"copy_to": "free_text_search",
"type": "keyword"
}
}
}
,
{
"long_as_keywords": {
"match": "*",
"match_mapping_type": "long",
"mapping": {
"copy_to": "free_text_search",
"type": "keyword"
}
}
}
],
"properties": {
"catagoryDesc": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"city": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"countryCode": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"countryName": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"domain": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"free_text_search": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"ipFrom": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipFromLong": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipTo": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipToLong": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipToProxyIndex": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipType": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"isp": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"proxyCatagory": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"proxyType": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"region": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
}
}
}
}
}
```

dadoonet · December 8, 2020, 8:35am

Your IP addresses don't have the ip datatype. You need to fix this before trying running ranges.

AKASH_DUBEY1 · December 8, 2020, 9:04am

If you see my query. I am querying the long field instead of IP field.

Christian_Dahlqvist · December 8, 2020, 9:05am

Why have you mapped this as keyword?

AKASH_DUBEY1 · December 8, 2020, 9:17am

mapping updated with long but still not working.I tried long, keyword, double no luck.

AKASH_DUBEY1 · December 8, 2020, 9:18am

keyword support range query.

Christian_Dahlqvist · December 8, 2020, 9:48am

Yes, but it may not give the expected results as it does not compare it as a number but rather character by character. An example is that 234 < 24 < 256.

AKASH_DUBEY1 · December 8, 2020, 12:06pm

If I execute the query with one range clause I have no issue adding second returns no result But as I already mentioned I tried with long and double also for given columns still no luck.

Christian_Dahlqvist · December 8, 2020, 12:16pm

Please provide a full reproduction including mappings, data as well as the queries you have tried and expected/unexpected results.

dadoonet · December 8, 2020, 12:31pm

There is no chance that 2888966725 matches something like 0:0:0:0:0:ffff:4131:6d83.

That's why it's failing.

Again, as Christian said and as I asked previously,

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

It should include mappings, etc...

And please format the code, and only the code. Not your questions, your text.

AKASH_DUBEY1 · December 8, 2020, 1:11pm

please read shared data first
POST http://localhost:9205/iptoproxy/1
'''
{
"ipToLong": 28147177550,
"ipFromLong": 28147177543,
"ipTo": "0:0:0:0:0:ffff:4131:6d83",
"ipFrom": "0:0:0:0:0:ffff:4131:6d83",
"proxyType": "DCH",
"countryCode": "JP",
"countryName": "Japan",
"region": "Tokyo",
"city": "Tokyo",
"isp": "Hurricane Electric LLC",
"domain": "he.net",
"proxyCatagory": "DCH",
"catagoryDesc": "Data Center/Web Hosting/Transit",
"ipType": null,
"ipToProxyIndex": "iptoproxy"
}
'''
BoolQueryBuilder boolQuery=QueryBuilders.boolQuery();
boolQuery.must(QueryBuilders.rangeQuery("ipFromLong").gte(ipLong));
boolQuery.must(QueryBuilders.rangeQuery("ipToLong").lte(ipLong));

above fields are used
please check the data added before replying.I have added mapping 1 record from sample ,use case,search query all in comments.
my simplest use case is "If my long value falls between ipFromLong and ipToLong .I should get the record but its not working with two range clauses in must condition.

AKASH_DUBEY1 · December 8, 2020, 1:15pm

PUT http://localhost:9205/iptoproxy

{
"iptoproxy": {
"mappings": {
"dynamic_templates": [
{
"free_as_keywords": {
"match": "free_text_*",
"match_mapping_type": "long",
"mapping": {
"copy_to": "free_text_search",
"type": "text"
}
}
}
,
{
"strings_as_keywords": {
"match": "*",
"unmatch": "free_text_*",
"match_mapping_type": "string",
"mapping": {
"copy_to": "free_text_search",
"type": "keyword"
}
}
}
,
{
"integer_as_keywords": {
"match": "*",
"match_mapping_type": "long",
"mapping": {
"copy_to": "free_text_search",
"type": "keyword"
}
}
}
,
{
"long_as_keywords": {
"match": "*",
"match_mapping_type": "long",
"mapping": {
"copy_to": "free_text_search",
"type": "keyword"
}
}
}
],
"properties": {
"catagoryDesc": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"city": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"countryCode": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"countryName": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"domain": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"free_text_search": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"ipFrom": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipFromLong": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipTo": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipToLong": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipToProxyIndex": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"ipType": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"isp": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"proxyCatagory": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"proxyType": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
},
"region": {
"type": "keyword",
"copy_to": [
"free_text_search"
]
}
}
}
}
}

POST http://localhost:9205/iptoproxy/1

{
"ipToLong": 28147177550,
"ipFromLong": 28147177543,
"ipTo": "0:0:0:0:0:ffff:4131:6d83",
"ipFrom": "0:0:0:0:0:ffff:4131:6d83",
"proxyType": "DCH",
"countryCode": "JP",
"countryName": "Japan",
"region": "Tokyo",
"city": "Tokyo",
"isp": "Hurricane Electric LLC",
"domain": "he.net",
"proxyCatagory": "DCH",
"catagoryDesc": "Data Center/Web Hosting/Transit",
"ipType": null,
"ipToProxyIndex": "iptoproxy"
}

POST http://localhost:9205/iptoproxy/_search

{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "ipFromLong": {
              "from": 28147177551,
              "to": null,
              "include_lower": true,
              "include_upper": false,
              "boost": 1
            }
          }
        },
        {
          "range": {
            "ipToLong": {
              "from": null,
              "to": 28147177551,
              "include_lower": true,
              "include_upper": true,
              "boost": 1
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}

Topic		Replies	Views
Java API and range queries Elasticsearch	6	1881	February 16, 2017
Query range lte return all data Elasticsearch	4	510	October 17, 2019
High level Java rest client date range greater than showing from and to in query formed Elasticsearch	9	5500	May 29, 2018
Range query not giving right result for lte Elasticsearch elastic-stack-sql	5	557	July 8, 2021
Range query is not working in elasticsearch JAVA Client API Elasticsearch	2	1248	June 24, 2019

Unable to get gte and lte from elastic Java API in range query

Related topics