Summing nested documents in Elasticsearch

Question

I'm doing a query that returns a bunch of documents. One of the fields in those documents is an array of dictionaries called hourly_values. I want to return the sum of the seconds field in that array of dictionaries, but I can't figure out how to get into the array and sum the values of all the seconds keys in each dictionary.

Here is what is stored in elasticsearch, and returned from a simple query:

{
  "took": 21,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "searchdb",
        "_type": "profile",
        "_id": "915",
        "_score": 1,
        "_source": {
          "id": 915,
          "market": "Chicago",
          "latitude": "41.1234",
          "longitude": "-87.5678",
          "structure_type": "Digital Display",
          "zip": 60654,
          "city": "Chicago",
          "player_id": 1234,
          "geo_location": {
            "lat": 41.1234,
            "lon": -87.5678
          },
          "hourly_values": [
            {
              "datetime": "2015-11-18T20:02:04Z",
              "seconds": 800
            },
            {
              "datetime": "2015-11-18T21:08:29Z",
              "seconds": 800
            },
            {
              "datetime": "2015-11-18T21:37:29Z",
              "seconds": 6400
            }
          ]
        }
      }
    ]
  }
}

and here is the query I'm trying to build:

{
    "size":0,
    "aggregations": {
        "seconds_agg": {
            "geo_distance": {
                "field": "geo_location",
                "origin":"41.893371,-87.628329",
                "unit":"km",
                "ranges":[
                    {
                        "from":0,
                        "to":20
                    }
                ]
            },
            "aggregations":{
                "ring_seconds_sum": {
                    "sum":{
                        "hourly_values":{
                            something goes here    
                        }       
                    }
                }
            }
        }
    }
}

I can't figure out what to put in the something goes here section of the query. Any ideas?

Sloan Ahrens · Accepted Answer · 2015-11-23 23:05:08Z

1

Try this (I changed "to":20 to "to":200 so it would match the document you posted):

POST /test_index/_search
{
   "size": 0,
   "aggregations": {
      "seconds_agg": {
         "geo_distance": {
            "field": "geo_location",
            "origin": "41.893371,-87.628329",
            "unit": "km",
            "ranges": [
               {
                  "from": 0,
                  "to": 200
               }
            ]
         },
         "aggregations": {
            "ring_seconds_sum": {
               "sum": {
                  "field": "hourly_values.seconds"
               }
            }
         }
      }
   }
}

Here's some code I used to test it:

http://sense.qbox.io/gist/02942c5753f6555dfab4571bf8f64bbc1dea74df

answered Nov 23, 2015 at 23:05

Sloan Ahrens

8,7382 gold badges32 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Summing nested documents in Elasticsearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related