# How to write a efficient ES query for the below SQL query?
**URL:** https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599
**Category:** Elasticsearch
**Created:** [January 19, 2020, 5:53am UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599 "2020-01-19T05:53:45Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@DeeeFOX](https://discuss.elastic.co/u/DeeeFOX)
#### Post date: [January 19, 2020, 5:53am UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/1 "2020-01-19T05:53:46Z")
```
select tag1, tag2, sum(sum_val)
select tag-uid, sum(value) as sum_val
from data_table
group by tag-uid
join tag_table using(tag-uid)
group by tag1, tag2
```
---
### Author:  [@aravindputrevu](https://discuss.elastic.co/u/aravindputrevu)
#### Post date: [January 19, 2020, 6:47am UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/2 "2020-01-19T06:47:05Z")
If you have your documents ingested appropriately like mentioned [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/_mapping_concepts_across_sql_and_elasticsearch.html), you could use the [translate](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html) endpoint of Elasticsearch SQL and convert the SQL query to equivalent Query DSL.
--
Aravind
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [January 19, 2020, 6:50am UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/3 "2020-01-19T06:50:08Z")
That seems to be a join which Elasticsearch does not support. You are therefore likely to restructure your data when indexing. If you describe the use case and what you are trying to achieve at a higher level someone may be able to help you.
---
### Author:  [@DeeeFOX](https://discuss.elastic.co/u/DeeeFOX)
#### Post date: [January 19, 2020, 1:54pm UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/4 "2020-01-19T13:54:50Z")
we use es as tsdb, and data we store is look like:
```
{
"metric": "metric_name",
"@timestamp": "2020-01-01T01:01:01",
"t": {
"key1": "val1",
"key2": "val2"
},
"f": {
"name1": 1.2,
"name2": 2.3
}
}
```
simple aggregation is ok (but some aggs are still very slow) like:
> select t.key2, sum(f.name1) from tsdata where t.key1='val1' group by t.key2
but i have no idea that if we can handle a more complex aggregation like "join" does in mysql
---
### Author:  [@DeeeFOX](https://discuss.elastic.co/u/DeeeFOX)
#### Post date: [January 19, 2020, 2:08pm UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/5 "2020-01-19T14:08:48Z")
oh, thanks for your replying!!
we use es as tsdb described in:
[my other answer](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/4)
data look like:
```
{
"metric": "metric_name",
"@timestamp": "2020-01-01T01:01:01",
"t": {
"key1": "val1",
"key2": "val2"
},
"f": {
"name1": 1.2,
"name2": 2.3
}
}
```
so, if we want to have more tsdb-use funcs like:
> 1、"topk( metric\_name.f.name1 ) by t.key1 in\_every '10m'"
> 2、"inc-rate( metric\_name.f.name1 ) in\_every '5m'"
that es aggs can not directly support
my question is:
can i enrich the function of es like udf do in hive ?
or, it's to say that how to create a user defined function in es?
---
### Author:  [@DeeeFOX](https://discuss.elastic.co/u/DeeeFOX)
#### Post date: [January 20, 2020, 4:53am UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/6 "2020-01-20T04:53:09Z")
Any ideas ?
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [February 17, 2020, 5:04am UTC](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599/7 "2020-02-17T05:04:28Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.