1

Hi I have denormalized the data to be flat in elasticsearch.

e.g.

{childId: 123, childAmount: 3.4, parentId: 1, parentAmount: 5.6}
{childId: 234, childAmount: 4.4, parentId: 1, parentAmount: 5.6}
{childId: 345, childAmount: 5.4, parentId: 2, parentAmount: 1.2}

See there are 3 children and 2 identical parent. How to calulate the sum amount of parentAmount (which should be 6.8)?

Thanks. And if possible, how to use kibana metric visual to show this data?

Xin
  • 33,823
  • 14
  • 84
  • 85

1 Answers1

2

In Kibana you can do it like this using a Metric visualization:

enter image description here

And with a query like this:

{
  "size": 0,
  "aggs": {
    "per_parent": {
      "terms": {
        "field": "parentId",
        "size": 25
      },
      "aggs": {
        "max": {
          "max": {
            "field": "parentAmount"
          }
        }
      }
    },
    "sum_amounts": {
      "sum_bucket": {
        "buckets_path": "per_parent>max"
      }
    }
  }
}
Andrei Stefan
  • 51,654
  • 6
  • 98
  • 89
  • Thanks. You are the man. That's exactly what I am looking for. give you 5 stars – Xin Dec 15 '17 at 01:24
  • Also may I know what ES version are you using? I found that `sum bucket` appear in 6.0 but not in 5.1 in my side. Thanks – Xin Dec 15 '17 at 01:25
  • Oh. I used the latest 5.6 version (I saw that you used the `kibana-5` tag in your post). – Andrei Stefan Dec 15 '17 at 04:03
  • I see. Thank you. – Xin Dec 15 '17 at 08:47
  • Note that there is a limit, say L, on the number of buckets that can be returned in the "terms" aggregation (I think the default L is around 65000, if I understood correctly). If your number of parents is over L then you will be getting wrong results using this approach, as there will be documents that will not be able to fall into one of those L buckets. – geo909 Dec 08 '20 at 22:55