Query documents by sum of nested field values in elasticsearch

Question

Rank beginner at ElasticSearch here.

I have a list of customers, and their orders as a nested field. Assuming a document structure like:

[
  { customerId: 123,
    birthday: 1980-01-01,
    orders: [
      {
        orderValue: 1500,
        orderDate: 2018-12-18T12:18:12Z
      },
      [...]
    ]
  },
  [...]
}

What I'd like to query is: The list of users who ordered for a certain amount from between two dates. And I'd like to be able to combine that with a range query for, for example, birthday.

I've gotten to the point where I can get the sum ordered between two dates per subscriber using aggregations:

{
  "size": 0,
  "aggs": {
    "foo": {
      "nested": {
        "path": "orders"
      },
      "aggs": {
        "grouped_by_customerId": {
          "terms": {
            "field": "orders.customerId.keyword"
          },
          "aggs": {
            "filtered_by_date": {
              "filter": {
                "range": {
                  "orders.orderDate": {
                    "from": "2018-01-28",
                    "to": null,
                    "include_lower": false,
                    "include_upper": true,
                    "format": "yyyy-MM-dd",
                    "boost": 1
                  }
                }
              },
              "aggs": {
                "sum": {
                  "sum": {
                    "field": "orders.orderValue"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

However, I'd like to limit the results I get back in the Query part, to mix better with all our other filters.

My first thought was to have a script filter and pass the bounding dates and minimum value in as parameters, but then I'd have to iterate over a doc's nested documents, and that doesn't seem to work.

Is that last idea possible, and if so, how?

Thanks!

I would clearly move the filter you have in your aggregation filter in the query part — Val
– Val, Commented Dec 23, 2018 at 7:46
Right, that part works fine. However, that is only part of the equation, as I also want to sum the orderValue's within that timeframe and compare them to a parameter. If I could loop over the orders-field (and check the from- and to-dates there as well, obviously) and compare it with my threshold, that would work, but that doesn't seem to be possible... — Bones
– Bones, Commented Dec 23, 2018 at 9:33
So right now, the aggregation you have brings you the correct sum of orders.orderValue, right? and now you just need to compare that sum with some parameter? — Val
– Val, Commented Dec 23, 2018 at 9:34
Correct. And preferably in the query, so it can mix-and-match with other selection criteria. I could do it in code, but I want the entire object (which could be quite large, and so could the result set) — Bones
– Bones, Commented Dec 23, 2018 at 9:51
Watch out for putting too many nested documents under a parent document. Whenever you add or modify a nested document the whole thing (parent and all it's nested documents) gets reindexed. This can cause a performance hit when the number of orders for a given user gets very high. — Radix Salvilines
– Radix Salvilines, Commented Jun 26, 2019 at 13:36

Bones · Accepted Answer · 2019-01-01 13:58:07Z

5

Finally solved this myself, using a Function Score query as follows:

{
  "query": {
    "bool": {
      "must": [
        {
          "function_score": {
            "min_score": 1,
            "query": {
              "nested": {
                "path": "orders",
                "ignore_unmapped": false,
                "score_mode": "min",
                "boost": 1,
                "query": {
                  "range": {
                    "orders.orderDate": {
                      "from": "2018-12-10",
                      "to": null,
                      "include_lower": true,
                      "include_upper": true,
                      "format": "yyyy-MM-dd",
                      "boost": 1
                    }
                  }
                }
              }
            },
            "functions": [
              {
                "filter": {
                  "match_all": {}
                },
                "script_score": {
                  "script": {
                    "source": "ArrayList x = params['_source']['orders'];if (x == null) { return 0 }long result = x.stream().filter(order -> {  if(params.startDate != null && !ZonedDateTime.parse(order.orderDate).isAfter(ZonedDateTime.parse(params.startDate))) return false; return true}).mapToLong(order->Long.parseLong(order.orderValue)).sum();if(params.operator == 'GT') return result > params.totalOrderValue ? 2 : 0;else if (params.operator == 'GE') return result >= params.totalOrderValue ? 3 : 0;else if (params.operator == 'LE') return result <= params.totalOrderValue ? 4 : 0;else if(params.operator == 'LT') return result < params.totalOrderValue ? 5 : 0;return result == params.totalOrderValue ? 6 : 0",
                    "lang": "painless",
                    "params": {
                      "totalOrderValue": 120,
                      "operator": "GE",
                      "startDate": "2012-12-10T23:00:00.000Z"
                    }
                  }
                }
              }
            ],
            "score_mode": "multiply",
            "max_boost": 3.4028235e+38,
            "boost": 1
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}

The actual scores this produces are debugging output to test the operator, but a min_score of 1 means any of them match. Using _source is quite slow.

Without a query in the function_score it works, but takes 20 seconds or so to muddle through 3 million records. With the query, you only look at customers with orders that actually match the date range.

Since the painless script processes the entire list of orders, it has to redo the date math. Some optimization to do there, but at least I have a Proof of Concept.

I've seen this question before without a satisfactory answer, so hopefully someone finds this useful.

answered Jan 1, 2019 at 13:58

Bones

818 bronze badges

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

3 Comments

Pierre Mallet Over a year ago

nice, thank a lot. I struggled with an equivalent problem. I had to filter matching document on a sum of filtered nested document value criteria (-_-!!), and since _source is not available in filter context, i had no way to do it. Good that the _source is available in score context !

nattfodd Over a year ago

@bones which version of ES are you using? params._source used to work in ES 5.6 but no longer works in 6.8 :(

Ron Serruya Over a year ago

The regular "boost_mode" multiples the score of the query with the score returned by the function, you might want to use "boost_mode": "replace" to use only the function score elastic.co/guide/en/elasticsearch/reference/current/…

Collectives™ on Stack Overflow

Query documents by sum of nested field values in elasticsearch

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