Kulasangar Kulasangar - 2 months ago 7
MySQL Question

How could I have MySQL sum() and group by clause within my elasticsearch query?

I'm trying perform an

elasticsearch
query as a
GET
request in order pull data from the index which I created. The data which is in the index is, a table from
MySQL
DB, configured though
logstash
.

Here is my request without the IN clause:

http://localhost:9200/response_summary/_search?q=api:"location"+AND+transactionoperationstatus:"charged"+AND+operatorid='DIALOG'+AND+userid:test+AND+time:"2015-05-27"

In the above, I should be able to append
sum(chargeAmount+0)
&
group by
. I tried giving it a search on the web, but couldn't find any solutions.

Any help could be appreaciated.

Val Val
Answer

Whatever you put after the q=... in your query uses the same syntax as a query_string query, so you can rewrite your query to leverage query_string and use aggregations to compute the desired sum:

curl -XPOST http://localhost:9200/response_summary/_search -d '{
   "query": {
       "query_string": {
           "query": "api:\"location\" AND transactionoperationstatus:\"charged\" AND operatorid:\"DIALOG\" AND userid:test AND time:\"2015-05-27\" AND responseCode:(401+403)"
       }
   },
   "aggs": {
      "total": {
          "sum": {
              "field": "chargeAmount"
          }
      }
   }
}'

In Postman, it would look like this:

enter image description here

Comments