# 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:** 1
**Showing post:** 4

<div class="post-metadata">

### Author: ![DeeeFOX](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/deeefox/32/49566_2.png) [@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")

</div>

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

---

_[View the full topic](https://discuss.elastic.co/t/how-to-write-a-efficient-es-query-for-the-below-sql-query/215599)._