CODEWITHSUNDEEP
mysqlsqlelasticsearchaggregation
deepak
deepak

Reputation: 3

Conversion from sql to elastic search query

How can i convert the following sql query into elastic search query?

SELECT sum(`price_per_unit`*`quantity`) as orders 
FROM       `order_demormalize` 
WHERE date(`order_date`)='2014-04-15'

Upvotes: 0

Views: 505

Answers (1)

bittusarkar
bittusarkar

Reputation: 6357

You need to use scripts to compute the product of values. For newer versions of Elasticsearch, enable dynamic scripting by adding the line script.disable_dynamic: false in elasticsearch.yml file. Note that this may leave a security hole in your Elasticsearch cluster. So enable scripting judiciously. Try the query below:

POST <indexname>/<typename>/_search?search_type=count
{
   "query": {
      "filtered": {
         "filter": {
            "term": {
               "order_date": "2014-04-15"
            }
         }
      }
   },
   "aggs": {
      "orders": {
         "sum": {
            "script": "doc['price_per_unit'].value * doc['quantity'].value"
         }
      }
   }
}

Upvotes: 1

Related Questions