Aggregating array of values in elasticsearch

Question

I need to aggregate an array as follows

Two document examples:

{
    "_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 the traveler has gone to. I want something like this:

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

Where the total is the length of the cities array minus 1. I tried the terms aggregation and the sum, but couldn't output the desired output.

Changes in the document structure can be made, so if anything like that would help me, I'd be pleased to know.

Do you have a mapping for that index that you can share?

Andrei Stefan
– Andrei Stefan

2014-11-04 21:59:39 +00:00
Commented Nov 4, 2014 at 21:59 — Andrei Stefan
– Andrei Stefan, Commented Nov 4, 2014 at 21:59

Dušan Maďar · Accepted Answer · 2017-04-24 14:23:26Z

17

in the document posted above "cities" is not a json array , it is a json object. If changing the document structure is a possibility I would change cities in the document to be an array of object

example document:

 cities : [
   {
     "name" :"saopaulo"
     "visit_count" :"2",

   },
   {
     "name" :"riodejaneiro"
     "visit_count" :"1",

   }
]

You would then need to set cities to be of type 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"
               }
            }
         }
      }

After which you could use nested aggregation to get the city count per user. The query would look something on these lines :

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

edited Apr 24, 2017 at 14:23

Dušan Maďar

10k6 gold badges58 silver badges72 bronze badges

answered Nov 4, 2014 at 22:05

keety

17.5k4 gold badges53 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrícia Villela Over a year ago

But wouldn't this double count "saopaulo", since I visited it on day 30 and day 31?

keety Over a year ago

@PatrickVillela yeah i misunderstood the question i edited the answer using cardinality elasticsearch.org/guide/en/elasticsearch/reference/current/… gives the total count of the distinct cities as wanted however one would have to subtact the -1 to account for total,probably total shouldn't be a part of "cities" object/field anyways and be a separate field outside

Patrícia Villela Over a year ago

Hello. Sorry for my delay, but I had to design a solution for another problem. I may return to this one later this week. I think it might work, though...

Collectives™ on Stack Overflow

Aggregating array of values in elasticsearch

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related