# Script Query - Returning multiple values on aggregation

**URL:** <https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095>\
**Category:** Elasticsearch\
**Created:** [January 12, 2022, 12:53am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095 "2022-01-12T00:53:53Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![hyun](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyun/32/97630_2.png) [@hyun](https://discuss.elastic.co/u/hyun)\
**Post date:** [January 12, 2022, 12:53am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/1 "2022-01-12T00:53:53Z")

</div>

**Aggregation Result**

```auto
"aggregations": {
      "649": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "ALLOGENE THERAPEUTICS",
               "doc_count": 43
            },
            {
               "key": "CELLECTIS",
               "doc_count": 5
            },
            {
               "key": "PFIZER",
               "doc_count": 4
            }
         ]
      }
   }

```

**Merge Aggregation Result**

```auto
"aggregations": {
      "649": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "STUB", // ALLOGENE THERAPEUTICS + CELLECTIS
               "doc_count": 48
            },
            {
               "key": "PFIZER",
               "doc_count": 4
            }
         ]
      }
   }

```

I want to merge "ALLOGENE THERAPEUTICS" and "CELLECTIS"

And change key name to "STUB"

Therefore, I made the following query using script.

The script query language is groovy.

my\_field, which is the aggregation target, is a list type.

```auto
{
  "size" : 0,
  "query" : {
    "ids" : {
      "types" : [],
      "values" : ["id1", "id2", "id3", "id4" ...]
    }
  },
  "aggregations" : {
    "649" : {
      "terms" : {
        "script" : {
          "inline" : 
              "def param = new groovy.json.JsonSlurper().parseText(
                  '{\"ALLOGENE THERAPEUTICS\": \"STUB\", \"CELLECTIS\": \"STUB\"}'
              ); 
              def data = doc['my_field'].values; 
              def list = [];
              if (!doc['my_field'].empty) { // my_field is list type
                  for (x in data) { 
                      if (param[x] != null) { 
                          list.add(param[x]); 
                      } 
                  } 
              }; 
              if (list.isEmpty()) { 
                  return data; // PFIZER
              } else { 
                  return list; // list["STUB", "STUB"]
              }"
        },
        "size" : 50
      }
    }
  }
}

```

According to the results, 48 STUB should be printed, but 47 STUB are being printed.

```auto
"aggregations": {
      "649": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "STUB", // ALLOGENE THERAPEUTICS + CELLECTIS
               "doc_count": 47 // It has to be 48 !!
            },
            {
               "key": "PFIZER",
               "doc_count": 4
            }
         ]
      }
   }

```

I've tried many things, but I think there's probably a problem with the list type.

I don't think I'm bringing all the elements.

I'd appreciate it if you could give me your opinion.

  
  

**++ Additional**

```auto
          def param = new groovy.json.JsonSlurper().parseText(
              '{\"ALLOGENE THERAPEUTICS\": \"STUB\", \"CELLECTIS\": \"STUB\"}'
          ); 
          def data = doc['my_field'].values; 
          def list = [];
          if (!doc['my_field'].empty) { // my_field is list type
              for (x in data) { 
                  if (param[x] != null) { 
                      list.add(param[x]); 
                  } else {
                      list.add(x);
                  }
              } 
          }; 
          return list;

```

It is the same even if I try with the corresponding script query.

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [January 12, 2022, 5:41am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/2 "2022-01-12T05:41:11Z")

</div>

Are there any documents with both A.. and C..?  
As it is "doc\_count", it does not double count documents with two STUBs. Can you identify the dropped document and share it?

If you want to just add the two values, sum bucket aggregation might be help.

---

<div class="post-metadata">

**Author:** ![hyun](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyun/32/97630_2.png) [@hyun](https://discuss.elastic.co/u/hyun)\
**Post date:** [January 12, 2022, 5:50am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/3 "2022-01-12T05:50:39Z")

</div>

> **[JSON Editor Online - view, edit and format JSON online](https://jsoneditoronline.org/#left=cloud.3767037b095449e58aaf9442b502dbdc)**
>
> JSON Editor Online is a web-based tool to view, edit, format, transform, and diff JSON documents.

This is the result of aggregation to top\_hits. It can be identified by documentId. How can I count in duplicate?

---

<div class="post-metadata">

**Author:** ![hyun](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyun/32/97630_2.png) [@hyun](https://discuss.elastic.co/u/hyun)\
**Post date:** [January 12, 2022, 5:53am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/4 "2022-01-12T05:53:51Z")

</div>

As you said, there is a document in which "A.." and "C.." overlap. That's why there's one missing. How can we include each of them in the "count"?

---

<div class="post-metadata">

**Author:** ![hyun](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyun/32/97630_2.png) [@hyun](https://discuss.elastic.co/u/hyun)\
**Post date:** [January 12, 2022, 5:55am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/5 "2022-01-12T05:55:38Z")

</div>

The duplicated document "\_id" is "us011072644b2".

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [January 12, 2022, 6:40am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/6 "2022-01-12T06:40:07Z")

</div>

I suppose you need alternative methods.

> [@Tomo\_M](#):
>
> If you want to just add the two values, sum bucket aggregation might be help.

or sum aggregation for runtime field with scrpt counting values may work.

---

<div class="post-metadata">

**Author:** ![hyun](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyun/32/97630_2.png) [@hyun](https://discuss.elastic.co/u/hyun)\
**Post date:** [January 12, 2022, 7:15am UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/7 "2022-01-12T07:15:44Z")

</div>

@Tomo_M Can you tell me how to use sum aggregation?  
In general, sum aggregation is used to calculate values of numeric types. How can I combine doc\_count with sum aggregation?

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [January 13, 2022, 1:19pm UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/9 "2022-01-13T13:19:49Z")

</div>

Alternative 1:

```auto
GET /test_sum_bucket_agg/_search
{
  "size":0,
  "aggs":{
    "f":{
      "filters": {
        "filters": {
          "all": {"match_all":{}}
        }
      },
      "aggs":{
        "sename":{
          "terms":{
            "field":"currentApplicantInfo.seName"
          }
        },
        "STUB":{
          "bucket_script": {
            "buckets_path": {
              "countA": "sename['ALLOGENE THERAPEUTICS']>_count",
              "countC": "sename['CELLECTIS']>_count"
            },
            "script": "params.countA + params.countC",
            "format": "#"
          }
        }
      }
    }
  }
}

```

```auto
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "f" : {
      "buckets" : {
        "all" : {
          "doc_count" : 3,
          "sename" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "ALLOGENE THERAPEUTICS",
                "doc_count" : 3
              },
              {
                "key" : "CELLECTIS",
                "doc_count" : 1
              },
              {
                "key" : "PFIZER",
                "doc_count" : 1
              }
            ]
          },
          "STUB" : {
            "value" : 4.0,
            "value_as_string" : "4"
          }
        }
      }
    }
  }
}

```

Alternative 2:

```auto
GET /test_sum_bucket_agg/_search
{
  "runtime_mappings": {
    "countAC": {
      "type": "long",
      "script":{
        "lang": "painless",
        "source": "int x=0; for (sename in doc['currentApplicantInfo.seName']){if ((sename == 'ALLOGENE THERAPEUTICS') || (sename == 'CELLECTIS')){x = x+1}} emit(x)"
      }
    }
  },
  "size":0,
  "fields": [
    {"field": "countAC"}
  ],
  "aggs":{
    "STUB":{
      "sum":{
        "field": "countAC",
        "format": "#"
      }
    },
    "terms":{
      "terms":{
        "field":"currentApplicantInfo.seName"
      }
    }
  }
}

```

```auto
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "terms" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "ALLOGENE THERAPEUTICS",
          "doc_count" : 3
        },
        {
          "key" : "CELLECTIS",
          "doc_count" : 1
        },
        {
          "key" : "PFIZER",
          "doc_count" : 1
        }
      ]
    },
    "STUB" : {
      "value" : 4.0,
      "value_as_string" : "4"
    }
  }
}

```

I also recommend to do it on the client side as he said on another topic.

> [@Elasticsearch - Merge date histogram aggregation](https://discuss.elastic.co/t/elasticsearch-merge-date-histogram-aggregation/294228/2):
>
> That's typically something to do on the client side. Not in Elasticsearch itself.

---

<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:** [February 10, 2022, 1:20pm UTC](https://discuss.elastic.co/t/script-query-returning-multiple-values-on-aggregation/294095/10 "2022-02-10T13:20:26Z")

</div>

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