Nested Aggregations

I was looking at aggregations today and found that aggregations had the same problem with grouping as doing an and between two fields in a sub document. This makes sense given the way Lucene stores multi field documents. When stored without nested it will group all values for a field together. I will walk through how to avoid some problems that can arise.

First lets set-up our data.

Create Schema with nested

	curl -XPUT 'http://localhost:9200/park_maintenance' -d '{
	"settings": {
	"number_of_shards": 5
	},
	"mappings": {
	"park": {
	"properties": {
	"park": {
	"properties": {
	"name": {
	"type": "string"
	},
	"cost": {
	"type": "long"
	},
	"tree": {
	"type": "nested",
	"properties": {
	"height": {
	"type": "long"
	},
	"species": {
	"type": "string"
	}
	}
	}
	}
	}
	}
	}
	}
	}'
	curl -X PUT 'http://localhost:9200/park_maintenance/_alias/pm'

view raw Nested_Aggs_Schema_Create hosted with ❤ by GitHub

Response

{"acknowledged":true}

view raw Response_ES_ack hosted with ❤ by GitHub

Index some nested documents

	curl -X POST localhost:9200/park_maintenance/park/central -d '{
	"park": {
	"name": "central",
	"cost": "5000",
	"tree": [
	{
	"height": 100,
	"species": "oak"
	},
	{
	"height": 70,
	"species": "oak"
	},
	{
	"height": 10,
	"species": "maple"
	}
	]
	}
	}'
	curl -X POST localhost:9200/park_maintenance/park/yellowstone -d '{
	"park": {
	"name": "yellowstone",
	"cost": "50000",
	"tree": [
	{
	"height": 99,
	"species": "oak"
	},
	{
	"height": 70,
	"species": "maple"
	},
	{
	"height": 10,
	"species": "dogwood"
	}
	]
	}
	}'

view raw Nested_Aggs_Index_park_nested hosted with ❤ by GitHub

Index some documents with a default mapping

	curl -X POST localhost:9200/park_maintenance_plain/park/central -d '{
	"park": {
	"name": "central",
	"cost": "5000",
	"tree": [
	{
	"height": 100,
	"species": "oak"
	},
	{
	"height": 70,
	"species": "oak"
	},
	{
	"height": 10,
	"species": "maple"
	}
	]
	}
	}'
	curl -X POST localhost:9200/park_maintenance_plain/park/yellowstone -d '{
	"park": {
	"name": "yellowstone",
	"cost": "50000",
	"tree": [
	{
	"height": 99,
	"species": "oak"
	},
	{
	"height": 70,
	"species": "maple"
	},
	{
	"height": 10,
	"species": "dogwood"
	}
	]
	}
	}'
	curl -X PUT 'http://localhost:9200/park_maintenance_plain/_alias/pmp'

view raw Nested_Aggs_Index_park_default_mapping hosted with ❤ by GitHub

Query for aggregations with the default mapping

	curl -X POST 'http://localhost:9200/pmp/park/_search?pretty=true' -d '{
	"query": {
	"match_all": {}
	},
	"size": 0,
	"aggs": {
	"species": {
	"terms": {
	"field": "species"
	},
	"aggs": {
	"total_height": {
	"sum": {
	"field": "height"
	}
	}
	}
	}
	}
	}'

view raw Nested_Aggs_Query_Without_Nested hosted with ❤ by GitHub

Response

	{
	"took" : 4,
	"timed_out" : false,
	"_shards" : {
	"total" : 5,
	"successful" : 5,
	"failed" : 0
	},
	"hits" : {
	"total" : 2,
	"max_score" : 0.0,
	"hits" : [ ]
	},
	"aggregations" : {
	"species" : {
	"buckets" : [ {
	"key" : "maple",
	"doc_count" : 2,
	"total_height" : {
	"value" : 359.0
	}
	}, {
	"key" : "oak",
	"doc_count" : 2,
	"total_height" : {
	"value" : 359.0
	}
	}, {
	"key" : "dogwood",
	"doc_count" : 1,
	"total_height" : {
	"value" : 179.0
	}
	} ]
	}
	}
	}

view raw Nested_Aggs_Query_Without_Nested_Response hosted with ❤ by GitHub

We can see that the numbers are way too high. There is only one dogwood tree in all the parks and it says the total height is 179. This because when it groups on dogwood, it cannot tell what height belongs to it. So for dogwood it gives the total for all the trees in the park.

To avoid this we need to make the tree type nested.
Query for aggregations with nested

	curl -X POST 'http://localhost:9200/pm/park/_search?pretty=true' -d '{
	"query": {
	"match_all": {}
	},
	"size": 0,
	"aggs": {
	"all_species": {
	"nested": {
	"path": "park.tree"
	},
	"aggs": {
	"species": {
	"terms": {
	"field": "species"
	},
	"aggs": {
	"total_height": {
	"sum": {
	"field": "height"
	}
	}
	}
	}
	}
	}
	}
	}'

view raw Nested_Aggs_Query_With_Nested hosted with ❤ by GitHub

Response

	{
	"took" : 6,
	"timed_out" : false,
	"_shards" : {
	"total" : 5,
	"successful" : 5,
	"failed" : 0
	},
	"hits" : {
	"total" : 2,
	"max_score" : 0.0,
	"hits" : [ ]
	},
	"aggregations" : {
	"all_species" : {
	"doc_count" : 6,
	"species" : {
	"buckets" : [ {
	"key" : "oak",
	"doc_count" : 3,
	"total_height" : {
	"value" : 269.0
	}
	}, {
	"key" : "maple",
	"doc_count" : 2,
	"total_height" : {
	"value" : 80.0
	}
	}, {
	"key" : "dogwood",
	"doc_count" : 1,
	"total_height" : {
	"value" : 10.0
	}
	} ]
	}
	}
	}
	}

view raw Nested_Aggs_Query_With_Nested_Response hosted with ❤ by GitHub

As you can see when I query on the nested index I get the expected counts.

Below I show a limitation of nest when doing aggregations. Since nested creates another Lucene under the covers it cannot reach back out of that Lucene index to aggregate data.

Query for aggregations with nested but, will reach outside the sub document

	curl -X POST 'http://localhost:9200/pm/park/_search?pretty=true' -d '{
	"query": {
	"match_all": {}
	},
	"size": 0,
	"aggs": {
	"all_species": {
	"nested": {
	"path": "park.tree"
	},
	"aggs": {
	"species": {
	"terms": {
	"field": "species"
	},
	"aggs": {
	"total_height": {
	"sum": {
	"field": "cost"
	}
	}
	}
	}
	}
	}
	}
	}'

view raw Nested_Aggs_Query_With_Nested_And_Reach_Outside hosted with ❤ by GitHub

Response

	{
	"took" : 6,
	"timed_out" : false,
	"_shards" : {
	"total" : 5,
	"successful" : 5,
	"failed" : 0
	},
	"hits" : {
	"total" : 2,
	"max_score" : 0.0,
	"hits" : [ ]
	},
	"aggregations" : {
	"all_species" : {
	"doc_count" : 6,
	"species" : {
	"buckets" : [ {
	"key" : "oak",
	"doc_count" : 3,
	"total_height" : {
	"value" : 0.0
	}
	}, {
	"key" : "maple",
	"doc_count" : 2,
	"total_height" : {
	"value" : 0.0
	}
	}, {
	"key" : "dogwood",
	"doc_count" : 1,
	"total_height" : {
	"value" : 0.0
	}
	} ]
	}
	}
	}
	}

view raw Nested_Aggs_Query_With_Nested_And_Reach_Outside_Response hosted with ❤ by GitHub

There are no results due to the field being summed not being found. To work around this the "terms" field would have to be moved out of the nested document. Alternatively, you can push the data being summed into each of nested documents.