Sum Query in Elasticsearch

Question

I'm fairly new to Elasticsearch. I'm trying to write a query that will group by a field and calculate a sum. In SQL, my query would look like this: SELECT lane, SUM(routes) FROM lanes GROUP BY lane

I have this data that looks like this in ES:

{
  "_index": "kpi",
  "_type": "mroutes_by_lane",
  "_id": "TUeWFEhnS9q1Ukb2QdZABg",
  "_score": 1.0,
  "_source": {
    "warehouse_id": 107,
    "date": "2013-04-08",
    "lane": "M05",
    "routes": 4047
  }
},
{
  "_index": "kpi",
  "_type": "mroutes_by_lane",
  "_id": "owVmGW9GT562_2Alfru2DA",
  "_score": 1.0,
  "_source": {
    "warehouse_id": 107,
    "date": "2013-04-08",
    "lane": "M03",
    "routes": 4065
  }
},
{
  "_index": "kpi",
  "_type": "mroutes_by_lane",
  "_id": "JY9xNDxqSsajw76oMC2gxA",
  "_score": 1.0,
  "_source": {
    "warehouse_id": 107,
    "date": "2013-04-08",
    "lane": "M05",
    "routes": 3056
  }
},
{
  "_index": "kpi",
  "_type": "mroutes_by_lane",
  "_id": "owVmGW9GT345_2Alfru2DB",
  "_score": 1.0,
  "_source": {
    "warehouse_id": 107,
    "date": "2013-04-08",
    "lane": "M03",
    "routes": 5675
  }
},
...

I want to essentially run the same query in ES as I did in SQL, so that my result would be something like (in json of course): M05: 7103, M03: 9740

imotov · Accepted Answer · 2013-04-17 16:15:54Z

6

In elasticsearch, you can achieve this by using terms stats facet:

{
    "query" : {
        "match_all" : {  }
    },
    "facets" : {
        "lane_routes_stats" : {
            "terms_stats" : {
                "key_field" : "lane",
                "value_field" : "routes",
                "order": "term"
            }
        }
    }
}

edited Apr 17, 2013 at 16:15

answered Apr 16, 2013 at 22:14

imotov

30.2k3 gold badges93 silver badges87 bronze badges

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

2 Comments

Shawn Roller Over a year ago

Thanks imotov. How do I sort the stat results? I'd like to sort by lane field, which is facets.lane_routes_stats.terms.term

imotov Over a year ago

All sort options are described in the documentation that I linked in my answer. I have also updated the answer with an example.

Collectives™ on Stack Overflow

Sum Query in Elasticsearch

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related