Q:

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'

A:

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"
         }
      }
   }
}

