Elasticsearch query filter nested array range min

1
votes

Given these documents:

{
  "id": "1"
  "prices": [
    {
      "param1": "A",
      "param2": "B",
      "total": 100 
    },
    {
      "param1": "A",
      "param2": "C",
      "total": 200 
    }
  ]
},
{
  "id": "2"
  "prices": [
    {
      "param1": "A",
      "param2": "B",
      "total": 200 
    },
    {
      "param1": "A",
      "param2": "C",
      "total": 300 
    }
  ]
},

How can they be filtered by prices range only with their minimum total value ?

Right now my query looks like:

{
  ...
  "query": {
    "bool": {
      "filter": [
        {
          "nested": {
            "path": "prices",
            "query": {
              "bool": {
                "filter": [
                  {
                    "range": {
                      "prices.total": {
                        "gte": 200,
                        "lte": 300
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

So it returns logically documents 1 and 2 because they both have a price in this range, but I only want to get document 2 because I want the filter logic only to be applied on the minimum price.

I've managed to do this in the ordering with "mode": "min", is there something similar for filtering ?

1
elasticsearch

1 Answers

0
votes

Since you don't know what you don't know when filtering for the minimum, you could theoretically come up with a query-time script to calculate that for you. But that'd be onerous and inefficient.

I'd instead recommend to calculate the local minimums before/at ingest time which'll dramatically speed up the lookup time.

You have more or less 3 options:

  1. Use an _update_by_query script to assign a top-level minTotalPrice to all your docs:
POST prices/_update_by_query
{
  "query": {
    "match_all": {}
  },
  "script": {
    "source": """
    def allTotals = ctx._source.prices.stream().map(group -> group.total).collect(Collectors.toList());
    ctx._source.minTotalPrice = Collections.min(allTotals)
    """,
    "lang": "painless"
  }
}

which greatly simplifies the actual range query:

GET prices/_search
{
  "query": {
    "range": {
      "minTotalPrice": {
        "gte": 200,
        "lte": 300
      }
    }
  }
}

  1. Use the same script but within an ingest pipeline. It can be applied when you're about to first ingest a document as well as when you're updating an individual document (for whatever reason).

  2. Use the copy_to mapping parameter as I explained here. But you'll then still need a query-time script to calculate the local minimum.