elasticsearch python queries - Group by field and then count

user_9254130 0
Viewed 2924

I am stuck regarding an elasticsearch query using python

I have data such as: 

{'_index': 'toto',
 '_type': 'tata',
 '_id': '9',
 '_version': 14,
 'found': True,
 '_source': {'Loss Event ID': 833,
  'Product': 'Sushi',
  'Company': 'SushiShop',
  'Profit': '10000000'}
}

{'_index': 'toto',
 '_type': 'tata',
 '_id': '11',
 '_version': 14,
 'found': True,
 '_source': {'Loss Event ID': 834,
  'Product': 'Burgers',
  'Company': 'McDonalds',
  'Profit': '4000000000'}
}

{'_index': 'toto',
 '_type': 'tata',
 '_id': '12',
 '_version': 14,
 'found': True,
 '_source': {'Loss Event ID': 836,
  'Product': 'Sushi',
  'Company': 'PlanetSushi',
  'Profit': '20000000'}
}

Goal: I would like to make a query using python - in keeping with group_by Product and count Profit to get this kind of result:

Product | Profit

-> Sushi = 30000000

-> Burgers = 4000000000

(...)

Any help? I tried python DSL but it failed 

enter code here

from time import time
from elasticsearch import Elasticsearch
from elasticsearch.helpers import bulk

import requests
res = requests.get('http://localhost:9200')
print(res.content)

#connect to our cluster
from elasticsearch import Elasticsearch
es = Elasticsearch([{'host': 'localhost', 'port': 9200}])

r = es.search(index='toto',
              doc_type='tata',
              body= {
    "query": { 
            "match" : { "Product": "Sushi" }
    },
    "aggs" : {
                "sum_income" : { "sum" : { "field" : "Profit" } }
    }
})

It fails... Tks

querydsl kibana-4 elasticsearch nosql python
1 Answers

Use below aggregation query to get total profit for each product.

{
    "size": 0,
     "aggs": {
      "product_name": {
          "terms": {
              "field": "Product"
          },
          "aggs": {
              "total_profit": {
                  "sum": {
                      "field": "Profit"
                  }
              }
          }
      }
  }
}

Note: Profit field must be any numeric type.

user_65232180
Related