# Calculations with values of different documents in painless query

**URL:** <https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [September 20, 2020, 7:31pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280 "2020-09-20T19:31:59Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [September 20, 2020, 7:31pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/1 "2020-09-20T19:31:59Z")

</div>

Hello

I would like to use a painless script to do some calculations. The problem is, the values are stored in different documents as time series. For example the index has a mapping of 2 fields fieldA and fieldB. There can be documents with values for both fields but usually documents with only one value for one field.  
How can I use a painless script to add values of two documents e.g. sum up the latest document for each field?

Example:  
Document 1:

```auto
{
"@timestamp": "2020-09-20T19:19:05.000Z",
"fieldA": 10
}

```

Document 2:

```auto
{
"@timestamp": "2020-09-20T19:19:06.000Z",
"fieldB": 20
}

```

Now I want to do something like this:

```auto
{
  "size": 1,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "gte": "now/d",
              "lt": "now/d+1d",
              "time_zone": "+02:00"
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "@timestamp": {
        "order": "desc"
      }
    }
  ],
  "_source": false,
  "script_fields": {
    "@script": {
      "script": {
        "lang": "painless",
        "source": "doc['fieldA'].value + doc['fieldB'].value"
      }
    }
  }
}

```

The result would be 20, because it is the most recent document.  
How can I use the painless script to find the most recent values for both fields?

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [September 21, 2020, 12:49am UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/2 "2020-09-21T00:49:05Z")

</div>

1. Should `fieldA` and `fieldB` really be strings? If yes, you'll need to use the `keyword` field for the calculation (and you don't want to turn on `fielddata` instead). Though maybe numbers are the better choice anyway?
2. You could either use a check `doc[<field>].size()==0` for missing fields or set it explicitly to 0 with `null_value` in the mapping.

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [September 21, 2020, 8:05am UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/3 "2020-09-21T08:05:18Z")

</div>

Oh, sorry, my bad. The numbers are stored as numbers of course. I will edit the first post.

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [September 21, 2020, 5:37pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/4 "2020-09-21T17:37:59Z")

</div>

Ok, then you need to work around missing fields. Does using `null_value` for 0 make sense? Otherwise you'll need a conditional.

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [September 21, 2020, 6:43pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/5 "2020-09-21T18:43:53Z")

</div>

Well I rather want to have the possibility that the query finds both most recent documents than excluding one of it because the calculation is useless with only one field value available...

How can a query request both independent documents and then running the script on the result?

The ugly workaround would be to parse the script query, extract the field name and do separate multi search queries for each field. Then interpret the painless script in the backend to return the result. But this would make the use of the painless script completely useless.

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [September 22, 2020, 2:34am UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/6 "2020-09-22T02:34:06Z")

</div>

In your initial query, there is a `"size": 1` and you also write about "the most recent document". That could be covered by a search operation.

Now it's about "both independent documents", which would need an aggregation.

It's a bit unclear what is the goal here.

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [September 22, 2020, 4:34pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/7 "2020-09-22T16:34:16Z")

</div>

Let's say there are several documents in the index. Some of them have only fieldA some of them only fieldB. They could be sensor values measured at slightly different timestamps. I want to calculate the sum or run other mathematical operations with the most recent values (within a time range) of _fieldA_ **and** _fieldB_ by using the script function.

How can I do this?

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [September 23, 2020, 4:27am UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/8 "2020-09-23T04:27:12Z")

</div>

I've tried to simplify it to the core parts — what about this:

```
PUT test/_doc/1
{
"@timestamp": "2020-09-20T19:19:05.000Z",
"fieldA": 10
}

PUT test/_doc/2
{
"@timestamp": "2020-09-20T19:19:06.000Z",
"fieldB": 20
}

GET test/_search
{
  "size": 0,
  "aggs": {
    "my_sum": {
      "scripted_metric": {
        "init_script": "state.elements = []", 
        "map_script": "state.elements.add(doc['fieldA'].size()>0 ? doc.fieldA.value : 0);state.elements.add(doc['fieldB'].size()>0 ? doc.fieldB.value : 0)",
        "combine_script": "long sum = 0; for (t in state.elements) { sum += t } return sum",
        "reduce_script": "long sum = 0; for (t in states) { sum += t } return sum"
      }
    }
  }
}

```

And it does return:

```
"aggregations" : {
  "my_sum" : {
    "value" : 30
  }
```

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [September 23, 2020, 8:49am UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/9 "2020-09-23T08:49:20Z")

</div>

Thanks! I will try it.  
Will it always find the most recent document based on @timestamp? How can I restrict it to a specific time range?

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [September 23, 2020, 11:54pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/10 "2020-09-23T23:54:00Z")

</div>

Add your initial `"query": {` between `size` and `aggs` to filter down to the documents you want to aggregate on.

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [September 29, 2020, 10:15pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/11 "2020-09-29T22:15:25Z")

</div>

This is the solution! Thank you! 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 27, 2020, 10:15pm UTC](https://discuss.elastic.co/t/calculations-with-values-of-different-documents-in-painless-query/249280/12 "2020-10-27T22:15:39Z")

</div>

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