Can ES do a complex aggregation with WHERE and GROUP BY + ORDER BY like in MySQL

[Long_Quanzheng (Quanzheng Long)]

Hi, I have a query like this in MySQL that I am thinking to migrate to ElasticSearch for scalability:

SELELCT userID,sum(countA) as sumA,sum(countB) as sumB
    FROM data_table 
 WHERE groupID=? AND date BETWEEN ? AND ? 
 GROUP BY userID 
 ORDER BY sumA 
LIMIT ?,?

For a table of id, groupID, date, userID, countA, countB with an index of <groupID, date>.

Is it possible to do it in ES? The closest featuer I can find is composite aggregation: Composite aggregation | Elasticsearch Reference [7.11] | Elastic

It seems supporting aggregation(group by), pagination(limit).
I guess sum(countA) can be done by sub-aggregation?

But it doesn't seem to support filtering by date/groupID.
Any suggestion?

[AClerk]

Yes.
Look at query dsl
Specifically, match for your groupID=?
range for your date between ? AND ?
sort for order by.
Cheers!

[Long_Quanzheng (Quanzheng Long)]

but it doesn't seem to support GROUP BY, did I miss something?
(Edited the subject of the post to be more clear)

[AClerk]

you can.
Look at the following

SQL like GROUP BY AND HAVING

Hi, I want to achieve some functionality which is available in SQL data stores. I have tried a lot but having a hard time achieving that functionality with elasticsearch. I am successful in creating the queries but the query does not seem to work correctly. I want to do: Group by based on some id Filter out groups with some condition Count the filtered results Tried on Elasticsearch 5.2.2 and 5.6.3 Basically I want to perform query similar to the following SQL: SELECT COUNT(*) FROM ( S…

Can elasticsearch do GROUP BY multi fields and ORDER BY count?

In SQL I would do it possibly like this: SELECT field1,field2, count(*) as cnt FROM table GROUP BY field1,field2 ORDER BY cnt DESC; Is aggregate query like that possible with ES?

And Terms Aggregation

[Long_Quanzheng (Quanzheng Long)]

HAVING is different from WHERE.

WHERE will do before grouping but HAVING is after.

Is it possible to apply the filtering before the aggregation?

[AClerk]

@Long_Quanzheng
All your requirements can be fulfilled with ELK.
I suggest that you will download, install and configure an instance and have a go.

[Long_Quanzheng (Quanzheng Long)]

Thanks!
I tested this SQL seems working:

curl -X GET "localhost:9200/_sql?format=txt&pretty=true" -H 'Content-Type: application/json' -d'
{
  "query" :"SELECT email,sum(countA) FROM \"library\" GROUP BY email LIMIT 2",
  "filter" :{
    "range" :{
       "number":{
         "gte":1,
         "lte":100,
       }
    }
  }
}
'

BTW, do you know how can I get the DSL version of this query?
I am very curious how the aggregation DSL works for this because the documentation doesn't have those feature working together.

[matriv (Marios Trivyzas)]

Why not:

curl -X GET "localhost:9200/_sql?format=txt&pretty=true" -H 'Content-Type: application/json' -d'
{
  "query" :"SELECT email,sum(countA) FROM \"library\" WHERE \"number\" between 1 and 100 GROUP BY email LIMIT 2"
}
'

and you can use the sql translate api to see the query dsl.