ElasticSearch Nested NumericRangeQuery using min value from list for comparison

Question

I have the following data:

[{
    "id": "1",
    "listItems": [
        {
            "key": "li1",
            "value": 100
        },
        {
            "key": "li2",
            "value": 5000
        }
    ]
},
{
    "id": "2",
    "listItems": [
        {
            "key": "li3",
            "value": 200
        },
        {
            "key": "li2",
            "value": 2000
        }
    ]
}]

I'm trying to do a NumericRangeQuery filter so that the MIN value in each document's listItems match up between a range. So for example, my range is 150 to 15000.

The only way I know how to write this is using a script query but it doesn't appear to work as the code still seems to grab any value under the listItems to attempt to match up against the range instead of grabbing the MIN like I told it to. Here's my query:

{
"track_total_hits": true,
"from": 0,
"min_score": 0.0,
"query": {
    "bool": {
        "must": [
            {
                "nested": {
                    "path": "listItems",
                    "query": {
                        "script": {
                            "script": "double minVal = 0; minVal = doc['listItems.value'][0]; for (wp in doc['listItems.value']) {if (wp < minVal) { minVal = wp;}} return minVal >= 150 && minVal <= 15000"
                        }
                    }
                }
            }
        ]
    }
}}

Anybody seeing something I don't?

did you get a chance to go through my answer, looking forward to get feedback from you :) — Bhavya
– Bhavya, Commented Sep 28, 2020 at 6:24
hey thank you for responding again, please see my question below. — user989953
– user989953, Commented Sep 28, 2020 at 13:06

Bhavya · Accepted Answer · 2020-09-26 03:23:43Z

1

The search query performs the following aggregation :

Terms aggregation on the id field
Min aggregation on listItems.value
Bucket Selector aggregation that is a parent pipeline aggregation that executes a script that determines whether the current bucket will be retained in the parent multi-bucket aggregation.

Adding a working example with index mapping, index data, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "listItems": {
        "type": "nested" 
      },
      "id":{
        "type":"text",
        "fielddata":"true"
      }
    }
  }
}

Index Data:

{
    "id" : "1",
    "listItems" : 
        [
            {
                "key" : "li1",
                "value" : 100
            },
            {
                "key" : "li2",
                "value" : 5000
            }
        ]
}
{
    "id" : "2",
    "listItems" : 
        [
            {
                "key" : "li3",
                "value" : 200
            },
            {
                "key" : "li2",
                "value" : 2000
            }
        ]
}

Search Query:

{
    "size": 0,
    "aggs": {
        "id_terms": {
            "terms": {
                "field": "id"
            },
            "aggs": {
                "nested_entries": {
                    "nested": {
                        "path": "listItems"
                    },
                    "aggs": {
                        "min_position": {
                            "min": {
                                "field": "listItems.value"
                            }
                        }
                    }
                },
                "value_range": {
                    "bucket_selector": {
                        "buckets_path": {
                            "totalValues": "nested_entries>min_position"
                        },
                        "script": "params.totalValues >= 150 && params.totalValues < 15000"
                    }
                }
            }
        }
    }
}

Search Result:

"aggregations": {
    "id_terms": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "2",
          "doc_count": 1,
          "nested_entries": {
            "doc_count": 2,
            "min_position": {
              "value": 200.0
            }
          }
        }
      ]
    }
  }

answered Sep 26, 2020 at 3:23

Bhavya

16.2k3 gold badges23 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user989953 Over a year ago

Hi thanks for responding again. So the only way to do this is through aggregation and not a typical document search query or filter? This does seem to work but it introduces a complexity to my existing code because my document is a lot larger than this with a lot more filtering than just simply trying to find the max min. We have separate logic for aggregation of the content and actually searching and return the document content for displaying (if that makes sense).

Bhavya Over a year ago

@user989953 Thanks for your reply :) I don't think this is possible using a typical search query or filter, as you want to find minimum value from each bucket (that has to be created using aggregation). And then on the buckets formed you want a range to be applied. So according to your use case and requirements, we can get the required result using aggregations only.

user989953 Over a year ago

I did go ahead and marked your response as answer as I was able to apply it to my code with a couple minor tweaks to make it work the way I need it. Thank you again for the help.

Collectives™ on Stack Overflow

ElasticSearch Nested NumericRangeQuery using min value from list for comparison

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related