Query to find sum of string fields


#1

Suppose I have records like this
{storename:s1, product:p1, status:available}
{storename:s1, product:p2, status:not available}
{storename:s1, product:p3, status:available}

{storename:s2, product:p1, status:not available}

I want to write a query to get the following result
storename:s1
available: 2
not available: 1

storename: s2
available: 1
not available: 0

Since status field is string, I am not sure how to take the sum of those fields.


(Daniel Mitterdorfer) #2

Hi @sruthi,

you have to use pipeline aggregations. This is a complete example that works with Elasticsearch 5.0:

PUT /store
{
   "mappings": {
      "product": {
         "properties": {
            "product": {
               "type": "keyword"
            },
            "status": {
               "type": "keyword"
            },
            "storename": {
               "type": "keyword"
            }
         }
      }
   }
}


PUT /store/product/1
{
    "storename": "s1",
    "product": "p1",
    "status": "available"
}

PUT /store/product/2
{
    "storename": "s1",
    "product": "p2",
    "status": "not available"
}

PUT /store/product/3
{
    "storename": "s2",
    "product": "p3",
    "status": "available"
}

GET /store/product/_search
{
   "query": {
      "match_all": {}
   },
   "size": 0,
   "aggs": {
      "stores": {
         "terms": {
            "field": "storename"
         },
         "aggs": {
            "status": {
               "terms": {
                  "field": "status"
               }
            }
         }
      }
   }
}

For 2.x, replace "type": "keyword" with:

"type": "string"
"index": "not_analyzed"

Daniel


(system) #3