Binding data with aggregations result into GroupBarChart with Vega

[pqt120583 (Thomas)]

Hi all,

I'm in trouble to binding this data to Vega group bar chart:

#data1:

[
{
"key": "morning",
"from": 0,
"to": 11,
"doc_count": 125,
"orders ": {
"value": 56
},
"customers": {
"value": 47
}
},
{
"key": "afternoon",
"from": 13,
"to": 18,
"doc_count": 25,
"orders": {
"value": 13
},
"customers": {
"value": 21
}
},
{
"key": "evening",
"from": 18,
"to": 24,
"doc_count": 35,
"orders": {
"value": 19
},
"customers": {
"value": 23
}
}
]

If following vega sample I need to transform data to the following :

#data2:

[
	{
	  "key": "morning",
	  "from": 0,
	  "to": 11,
	  "doc_count": 125,
	  "position": 0,
	  "value": 56,
	  "name": "orders"
	},	
	{
	  "key": "morning",
	  "from": 0,
	  "to": 11,
	  "doc_count": 125,
	  "position": 1,
	  "value": 47,
	  "name": "customers"
	},
	{
	  "key": "afternoon",
	  "from": 13,
	  "to": 18,
	  "doc_count": 25,
	  "position": 0,
	  "value": 13,
	  "name": "orders"
	},
	{
	  "key": "afternoon",
	  "from": 13,
	  "to": 18,
	  "doc_count": 25,
	  "position": 0,
	  "value": 21,
	  "name": "customers"
	},
	{
	  "key": "evening",
	  "from": 18,
	  "to": 24,
	  "doc_count": 35,
	  "position": 0,
	  "value": 19,
	  "name": "orders"
	},
	{
	  "key": "evening",
	  "from": 18,
	  "to": 24,
	  "doc_count": 35,
	  "position": 1,
	  "value": 23,
	  "name": "customers"
	}
]

Is it possible to use vega transform to convert the data #1 to data #2 ?
Or is there any other implemetation which can binding this data to group bar chart with VEGA ?

Anyway, I can display this data by using the build-in chart in kibana easily but I want to use VEGA for the UI customization.

Thanks,
Thomas.

[tsullivan (Tim Sullivan)]

Hi, when you need data to come back in a tabular format, Elasticsearch's composite aggregation can be very useful: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html

Say I have data that looks like:

{
  "_source": {
    "@date": "2018-05-30T07:19:46Z",
    "key": "dinner",
    "orders": {
      "metric": 721
    }
  }
},
{
  "_source": {
    "@date": "2018-05-29T13:46:28Z",
    "key": "lunch",
    "customers": {
      "value": 433
    }
  }
},

A composite aggregation can return the data in a tabular format:

POST /restaurant/_search?size=0
{
  "aggs": {
    "table": {
      "composite": {
        "sources": [
          {
            "date": {
              "date_histogram": { "field": "@date", "interval": "day" }
            }
          },
          {
            "customers": {
              "terms": { "field": "customers.value" }
            }
          },
          {
            "orders": {
              "terms": { "field": "orders.value" }
            }
          }
        ]
      }
    }
  }
}

You can use any kind of bucket aggregation as source aggregations.

[pqt120583 (Thomas)]

Thank tsullivan,

I have tried to use the composite aggregation, but i cannot make range aggregations as a item of source.

Here's my previous query to get the result with aggregations:

GET test/_search
{
  "size":0,
	"_source":{"excludes":[]},
	"aggs":{
		"hours":{
			"range":{
				"script":{ "inline":"doc['logTimeStamp'].date.hourOfDay","lang":"painless"},
				"ranges":[
					{"key": "morning" , "from":0,"to":11},
					{"key": "afternoon", "from":13,"to":18},
					{"key": "evening",  "from":18,"to":24}					
				]},
				"aggs":{
					"orders":{
						"cardinality":{"field":"order.orderCode.keyword"}
					}
					,"customers":{
						"cardinality":{"field":"customer.customerCode.keyword"}
					}
				}
		}
	}
}

Do you have any idea of converting this query to return the tabular data ?

Many thanks,
Thomas.

[tsullivan (Tim Sullivan)]

Unfortunately, composite it pretty strict about what kind of aggregations can be in the source aggregations, and it also isn't possible to have a parent aggregation around a composite aggregation.

If it is at all possible, you'd want to have an hours field pre-calculated and be part of the document at index time.