Elasticsearch query filter nested array range min

Question

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 ?

score 0 · Answer 1 · answered Feb 08 '21 at 13:36

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:

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
      }
    }
  }
}

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).
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.

Thanks or the interesting resources. I have already thought about specifying the value in the document but hoped there would be a more direct approach, because I need optional additional filters to be passed regarding prices, so I'd have to specify the minTotalPrice for each filtering scenario, which is totally doable and certainly more efficient than computing that at query time. — Matthieu Dsprz, Feb 08 '21 at 14:33
You're welcome. Yea -- go put them all onto the top level. It's worth the effort as opposed to querying them while nested! — Joe - GMapsBook.com, Feb 08 '21 at 15:41

Elasticsearch query filter nested array range min

1 Answers1

You have more or less 3 options: