Exclude null containing key (records) while executing range Query in Elastic search JAVA Rest client

Elastic Stack Elasticsearch
1

Let, I have below six records in my index.

{
        "min": "300",
        "max": "350",
        "contentName": "Dabangg 5"
    },
    {
        "min": "150",
        "max": "200",
        "contentName": "Dabangg 2"
    },
    {
        "min": "400",
        "max": "500",
        "contentName": "Dabangg 4"
    },
    {
        "min": null,
        "max": null,
        "contentName": "Dabangg 6"
    },
    {
        "min": "100",
        "max": "200",
        "contentName": "Dabangg"
    },
    {
        "min": "160",
        "max": "219",
        "contentName": "Dabangg 3"
    }

I am using the following rangeQuery to get the results by "min" and "max" field, which is working fine and I am getting proper results.

BoolQueryBuilder boolQuery1 = QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("max").gte("100"))
  .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("max")));

BoolQueryBuilder boolQuery2 = QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("min").lte("300"))
  .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("min")));

But in every result I am getting the record which is having "max" and "min" as null(Example: Dabangg 6, according to above data set), which I want to exclude.

So, can anyone help me with this requirement.

Thanks

2

Hi,

Two "should" clauses in a Boolean query work like an OR. That is, boolQuery1 is searching for all documents where the max is greater than 100 OR documents where the max field does not exist.

I believe what you're looking for are must clauses at the top level:

BoolQueryBuilder boolQuery3 = QueryBuilders.boolQuery()
  .must(QueryBuilders.rangeQuery("max").gte("100"))
  .must(QueryBuilders.existsQuery("max"));

I haven't tested this Java code directly, but I have tested the following in the Kibana console:

GET dabangg/_search
{
  "query": {
    "bool": {
      "must": [
        { "range": { "max": { "gte": 250 } } },
        { "exists": { "field": "max" } }
      ]
    }
  }
}

I hope this helps,

-William

1 Like
3

Thank you very much @William_Brafford . This is what I've been looking for. Worked.

4

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