# Sql Query to ELasticSearch query

**URL:** <https://discuss.elastic.co/t/sql-query-to-elasticsearch-query/87617>\
**Category:** Elasticsearch\
**Created:** [May 30, 2017, 6:58pm UTC](https://discuss.elastic.co/t/sql-query-to-elasticsearch-query/87617 "2017-05-30T18:58:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yur\_Gasparyan](https://avatars.discourse-cdn.com/v4/letter/y/7c8e57/32.png) [@Yur\_Gasparyan](https://discuss.elastic.co/u/Yur_Gasparyan)\
**Post date:** [May 30, 2017, 6:58pm UTC](https://discuss.elastic.co/t/sql-query-to-elasticsearch-query/87617/1 "2017-05-30T18:58:41Z")

</div>

Hi all. I am going to use elasticsearch for my app and in my case I have sql query look like this  
SELECT activities.activity,  
activities.time,  
r._,  
( ( ( MATCH(r.title)AGAINST( 'test_' IN BOOLEAN MODE )+( ( MATCH(r.title)AGAINST( '"test\*"' IN BOOLEAN MODE ) )\* 2 ) )\* 1.5 )+( MATCH(r.body)AGAINST( 'test\*' IN BOOLEAN MODE )+( ( MATCH(r.title)AGAINST( '"test\*"' IN BOOLEAN MODE ) )\* 2 ) )+( MATCH(tags.`name`)AGAINST( 'test\*' IN BOOLEAN MODE )+(  
( SELECT (SUM( 'test\* test\*\*' REGEXP CONCAT( '[[:\<:]]', tags.`name`, '[[:\>:]]' )) )AS tag\_score  
FROM results  
INNER JOIN tags ON [results.id](http://results.id) = tags.result\_id  
WHERE [results.id](http://results.id) = [r.id](http://r.id)  
AND published != 2  
GROUP BY [results.id](http://results.id)  
ORDER BY tag\_score DESC  
LIMIT 1 ) \* 2) ) )AS score,  
count(tags.`name`)AS tags\_count  
FROM results AS r,  
tags,  
activities  
WHERE [r.id](http://r.id) = tags.result\_id  
AND activities.result = [r.id](http://r.id)  
AND (r.scope = 'a2'  
OR r.scope = 'a1')  
GROUP BY [r.id](http://r.id)  
HAVING score \> 0  
ORDER BY score DESC  
LIMIT 8  
OFFSET 0

Please help me to convert this query to elastic search query.. (with Logstash SQL).. Thank you very much

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2017, 8:10am UTC](https://discuss.elastic.co/t/sql-query-to-elasticsearch-query/87617/2 "2017-05-31T08:10:00Z")

</div>

As Elasticsearch is not a relational system and do not support joins, there is no like-for-like translation of SQL queries. I therefore suspect it will be difficult for anyone to give an answer based on the information you have provided.

When using Elasticsearch, you are likely to need to change the way you model your data, so it is probably easier to get help if you describe what your data looks like and represents as well as how you want to be able to query it.

---

<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:** [June 28, 2017, 8:10am UTC](https://discuss.elastic.co/t/sql-query-to-elasticsearch-query/87617/3 "2017-06-28T08:10:14Z")

</div>

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