Group By and Sum the values in each group


(Thirunavukkarasu Ravichandran) #1

Hi,

I need get the sum of each item quantity? My data:

{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 21,
    "successful": 21,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "bill",
        "_type": "bill",
        "_id": "2650",
        "_score": 1,
        "_source": {
          "id": 2650,
          "name": "Order 1",
          "orders": [
            {
              "id": 3200,
              "item": {
                "id": 3600,
                "name": "Barbeque",
                "price": 175
              },
              "quantity": 1,
              "created": "2016-08-20 21:01:09"
            },
            {
              "id": 3201,
              "item": {
                "id": 3601,
                "name": "Pepsi",
                "price": 25,
                },
              "quantity": 1,
              "created": "2016-08-20 21:01:15"
            },
            {
              "id": 3202,
              "item": {
                "id": 3602,
                "name": "Beer",
                "price": 120
              },
              "quantity": 1,
              "created": "2016-08-20 21:01:20"
            }
          ]
        }
      },
      {
        "_index": "bill",
        "_type": "bill",
        "_id": "3000",
        "_score": 1,
        "_source": {
          "id": 3000,
          "name": "Order 2",
          "orders": [
            {
              "id": 3203,
              "item": {
                "id": 3602,
                "name": "Beer",
                "price": 120
              },
              "quantity": 2,
              "created": "2016-08-20 15:34:09"
            },
            {
              "id": 3204,
              "item": {
                "id": 3601,
                "name": "Pepsi",
                "price": 25
              },
              "quantity": 1,
              "created": "2016-08-20 21:04:23"
            }
          ]
        }
      }
    ]
  }
}

The output should be like:
Item name Quantity
Pepsi 2
Beer 3
Barbeque 1

Can you help me get this data table in kibana?


(Mark Walkom) #2

Please format your code with the </> button, it’s very hard to read.


(CJ Cenizal) #3

Hi there,

After you create a "Data Table" visualization, I think you'll need to do these two steps:

  1. Twirl down the Metric aggregation and change it from "Count" to "Sum". Change the field to "quantity".
  2. Beneath "buckets", select the "Split Rows" bucket type. Choose a "Terms" aggregation and set the field to "item.name".

When you hit the play button to create the visualization, you should see the data table you're looking for.

Thanks,
CJ


(system) #4