# Elasticsearch query vs. SQL query

**URL:** <https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187>\
**Category:** Elasticsearch\
**Created:** [May 4, 2016, 2:17pm UTC](https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187 "2016-05-04T14:17:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![diwertowski](https://avatars.discourse-cdn.com/v4/letter/d/ac91a4/32.png) [@diwertowski](https://discuss.elastic.co/u/diwertowski)\
**Post date:** [May 4, 2016, 2:17pm UTC](https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187/1 "2016-05-04T14:17:46Z")

</div>

Is it possible to do this sql query:

```
SELECT icID, icmID, message from logstash*
WHERE @timesstamp in (
  SELECT @timestamp from logstash*
  WHERE icID = 8676 )

```

with a query from elasticsearch? The problem is, that i can't do a subquery with elasticsearch if I don't want to type in my special @timestamp value

```
{
"query": {
   "bool": {
      "must": {
            "bool": {
                  "should": [
                      {
                        "match": {
		            "@timestamp": {
				"query": "2016-04-27T05:01:20.055Z",
				"type": "phrase"
			     }
			}
		   },
		   {
               ...
		
	"_source": {
		"includes": [
			"icID",
			"message"
		],
		"excludes": []
	}
}

```

This is want I don't want.

Can someone help me out with this problem? Or is it impossible to do a "subquery" in elasticsearch?

Thanks for your help!

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [May 5, 2016, 11:05pm UTC](https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187/2 "2016-05-05T23:05:40Z")

</div>

You can't do an `in` like that, no.  
But why not just filter based on that `icID` anyway?

---

<div class="post-metadata">

**Author:** ![diwertowski](https://avatars.discourse-cdn.com/v4/letter/d/ac91a4/32.png) [@diwertowski](https://discuss.elastic.co/u/diwertowski)\
**Post date:** [May 6, 2016, 6:52am UTC](https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187/3 "2016-05-06T06:52:34Z")

</div>

Thank you @warkolm! I think I'll show you my logs and maybe you can see, why that's not working for me.  
Here are my Logs:

```
2016-04-27 07:02:22,000 -2- [TEST] ic=0007
2016-04-27 07:01:20,024 -2- [TEST] ic=7211
...
2016-04-27 07:01:20,024 -3- [HELLOCLASS] hello.
...
2016-04-27 07:02:59,999 -2- [TEST] ic=8888
2016-04-27 07:03:00,000 -2- [TEST] ic=9999

```

So as you can see I've different `icIDs`, but thats not the problem. My problem is, how I get this line with `hello.` In this line I've nothing else as the same timestamp as in line 2. That's why i can't only filter on `icID`.  
This is only an example, there are a lot more lines than this `hello.`-line with different content, but with the same timestamp.  
The best solution is, if I can type in the `icID` and get all results with the same timestamp.  
Any suggestions? Thanks for your help!

---

<div class="post-metadata">

**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\
**Post date:** [May 6, 2016, 10:50am UTC](https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187/4 "2016-05-06T10:50:06Z")

</div>

Keep in mind, ES is a search engine, not a relational database engine so when you try to make ES works like a DB, in some cases, it's challenging. You need to look at the model that is indexed in ES and might need to adjust that model in order to achieve what you could do with a DB.

So, based on your post, the indexed document in ES seems to have two fields: icID and message. Based on your logs, each line is a document where message field holds the entire line, correct?

- To get the hello line, you can simple search for hello

- To get a specific line, you can search icID:[value]

If you break each line into multiple fields to hold the timestamp, the value "-2- or -3-", the value "[TEST] or [HELLOCLASS]", the value "ic=####", etc... it means you are adjusting the model, this allows you to perform other type of search and hopefully you'll be able to get what you want.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:53pm UTC](https://discuss.elastic.co/t/elasticsearch-query-vs-sql-query/49187/5 "2017-07-05T22:53:22Z")

</div>