Aggregating an array of values ​​in elasticsearch

Question

I need to assemble an array as follows

Two sample documents:

{ "_index": "log", "_type": "travels", "_id": "tnQsGy4lS0K6uT3Hwzzo-g", "_score": 1, "_source": { "state": "saopaulo", "date": "2014-10-30T17", "traveler": "patrick", "registry": "123123", "cities": { "saopaulo": 1, "riodejaneiro": 2, "total": 2 }, "reasons": [ "Entrega de encomenda" ], "from": [ "CompraRapida" ] } }, { "_index": "log", "_type": "travels", "_id": "tnQsGy4lS0K6uT3Hwzzo-g", "_score": 1, "_source": { "state": "saopaulo", "date": "2014-10-31T17", "traveler": "patrick", "registry": "123123", "cities": { "saopaulo": 1, "curitiba": 1, "total": 2 }, "reasons": [ "Entrega de encomenda" ], "from": [ "CompraRapida" ] } }, 

I want to aggregate the cities array to find out all the cities traveler went to. I want something like this:

 { "traveler":{ "name":"patrick" }, "cities":{ "saopaulo":2, "riodejaneiro":2, "curitiba":1, "total":3 } } 

Where total is the length of the cities array minus 1. I tried the aggregation of terms and the sum, but could not output the desired result.

Changes in the structure of the document can be made, so if something like this helps me, I would be happy to know.

Answer 1

in the city document above is not a json array, it is a json object. If changing the structure of the document is possible, I would change the cities in the document as an array of objects

document example:

  cities : [ { "name" :"saopaulo" "visit_count" :"2", }, { "name" :"riodejaneiro" "visit_count" :"1", } ] 

Then you need to set the cities to be nested in the index mapping

  "mappings": { "<type_name>": { "properties": { "cities": { "type": "nested", "properties": { "city": { "type": "string" }, "count": { "type": "integer" }, "value": { "type": "long" } } }, "date": { "type": "date", "format": "dateOptionalTime" }, "registry": { "type": "string" }, "state": { "type": "string" }, "traveler": { "type": "string" } } } } 

Then you can use the nested aggregation to get the city account per user. The query will look something like this:

 { "query": { "match": { "traveler": "patrick" } }, "aggregations": { "city_travelled": { "nested": { "path": "cities" }, "aggs": { "citycount": { "cardinality": { "field": "cities.city" } } } } } }