Conversion from sql to elastic search query

Question

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'

Cleaned up formatting

James A Mohler
– James A Mohler

2015-03-23 05:35:07 +00:00
Commented Mar 23, 2015 at 5:35 — James A Mohler
– James A Mohler, Commented Mar 23, 2015 at 5:35

bittusarkar · Accepted Answer · 2015-03-23 06:38:26Z

1

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

edited Mar 23, 2015 at 6:38

answered Mar 23, 2015 at 6:00

bittusarkar

6,3674 gold badges36 silver badges51 bronze badges

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

5 Comments

deepak Over a year ago

I am not able to access script ie doc['price_per_unit'].value .

deepak Over a year ago

Is there any other way to do this?

bittusarkar Over a year ago

What version of Elasticsearch are you using?

deepak Over a year ago

I am using elasticsearch - 1.4.4

bittusarkar Over a year ago

Then you need to enable scripting by adding the line script.disable_dynamic: false in elasticsearch.yml file

Collectives™ on Stack Overflow

Conversion from sql to elastic search query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related