Aggregation across multiple indices

Elastic Stack Elasticsearch
1

I have 2 indices and they store data of the same mappings on different dates - t1 and t2.

I have a multi term aggregation below which works on a single index. How can I get elasticsearch to return the same aggregation across t1 and t2 as well as do some comparison like t1.total-price - t2.total-price?

    PUT t1/_mappings/
    {
      "properties": {
        "account": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
    "price": {
          "type": "double",
          "null_value": 0
        }
    }

    GET /t1/_search
    {
      "size": 0,
         "aggs": {
            "group-by-account": {
              "terms": {
                "field": "account.keyword"
              },
              "aggs": {
                "total-price": {
                  "sum": {
                    "field": "price"
                  }
                }
              }
            }
      }
2

You can use GET t1,t2/_search or even GET t*/_search to search across multiple indices. See Search multiple data streams and indices | Elasticsearch Guide [7.12] | Elastic

3

Yes I got that. I am able to get t1 and t2 returned together but I am not sure how I can calculate the difference between aggregated results across the 2 indices. Any ideas on that?

4 
    GET /t1,t2/_search
    {
      "size": 0,
      "aggs": {
        "byindex": {
          "terms": {
            "field": "_index",
            "size": 2
          },
          "aggs": {
            "group-by-account": {
              "terms": {
                "field": "account.keyword"
              },
              "aggs": {
                "total-price": {
                  "sum": {
                    "field": "price"
                  }
                }
              }
            }
          }
        }
      }
    }
5

Can you elaborate more on the differences you want to aggregate between?

6

I am trying to create the data in the following fashion.

index: t1
|Account|Price|
|a|100|
|b|200|
|c|300|

index: t2
|Account|Price|
|a|200|
|b|400|
|c|600|

results of t2 -t1
|Account|Price|
|a|100|
|b|200|
|c|300|

Below is the response I get currently. However, I am trying to get the difference between sum(price) across t1 and t2 group by the account

     "aggregations" : {
        "byindex" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : "t1",
              "doc_count" : 135212,
              "group-by-account" : {
                "doc_count_error_upper_bound" : 1123,
                "sum_other_doc_count" : 56391,
                "buckets" : [
                  {
                    "key" : "a",
                    "doc_count" : 13145,
                    "mtm" : {
                      "price" : 5.343518269844282E7
                    }
                  },
          {
              "key" : "t2",
              "doc_count" : 135212,
              "group-by-account" : {
                "doc_count_error_upper_bound" : 1123,
                "sum_other_doc_count" : 56391,
                "buckets" : [
                  {
                    "key" : "a",
                    "doc_count" : 13145,
                    "mtm" : {
                      "value" : 5.343518269844285E7
                    }
                  },
7

Could someone please advise here?

8

Hi ice2021,
it is perfectly doable with a Bucket script aggregation | Elasticsearch Guide [7.12] | Elastic
The tricky part is the bucket path: follow the link in the mentioned doc to "bucket path syntax".
Tip: the account should be your top aggregation. Then, for each account you can aggregate by index, and get the sum of prices. your final bucket-script-aggregation will just substract the 2 sums, using bucket paths which contain the index names (that you should know).

1 Like
9

Got it working. Thanks

10

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