CODEWITHSUNDEEP
mysqlsqlsql-serverelasticsearch
Nathan Miranda
Nathan Miranda

Reputation: 79

How to convert this SQL query to an Elasticsearch query?

I'm new to Elasticsearch querying, so I'm a little lost on how to convert this SQL query to an Elasticsearch query:

SELECT time_interval, type, sum(count)  
FROM test  
WHERE  (&start_date <= t_date <= &end_date) 
GROUP BY time_interval, type

I know I can use the "range" query to set parameters for gte and lte, but if there's a clearer way to do this, that would be even better. Thanks in advance!

Edit:

My elasticsearch is setup to have an index: "test" with type: "summary" and contains JSON documents that have a few fields:

t_datetime

t_date

count

type

*t_id**

The IDs for these JSON documents are the t_date concatenated with the t_id values

Upvotes: 1

Views: 1098

Answers (1)

bittusarkar
bittusarkar

Reputation: 6357

Assuming, t_datetime is the same as time_interval, you can use the query below:

POST trans/summary/_search?search_type=count
{
   "aggs": {
      "filtered_results": {
         "filter": {
            "range": {
               "t_date": {
                  "gte": "2015-05-01",
                  "lte": "2015-05-30"
               }
            }
         },
         "aggs": {
            "time_interval_type_groups": {
               "terms": {
                  "script": "doc['t_datetime'].value + '_' + doc['type'].value",
                  "size": 0
               },
               "aggs": {
                  "sum_of_count": {
                     "sum": {
                        "field": "count"
                     }
                  }
               }
            }
         }
      }
   }
}

This query is making use of scripts. On newer versions of Elasticsearch, dynamic scripting is disabled by default. To enable dynamic scripting, follow this.

Upvotes: 0

Related Questions