How to get the data with concept in SQL "Group by"

Elastic Stack Elasticsearch
1

Hi every one,

I would like to query the data by using the concept in SQL "Group by" like below

"SELECT user, DATE(timestamp), EXTRACT(HOUR FROM timestamp), floor_name FROM location WHERE DATE(timestamp) BETWEEN "(d1) " AND "(d2) " GROUP BY DATEtimestamp), EXTRACT(HOUR FROM timestamp), user, floor_name"

However in Elasticsearch, no matter I use "terms aggregation" or "date_histogram", I got the count number....while I want to query the data than the count like below

user | date| hour |floor_name
Jason| 2020-04-19 | 7 | Floor 1
Jason| 2020-04-19 | 8| Floor 1
Jason| 2020-04-19 | 9| Floor 2
Louisa | 2020-04-19 | 8 | Floor 2
Louisa | 2020-04-19 | 9| Floor 5

group by the date hour of a user.

May I know how to write a ES queries to give the above result ? many thanks.

2

The SQL Group By queries are translated to composite aggregations. You could use the translate SQL endpoint to see how your SQL query translates to ES query DSL.

1 Like
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.