Sql Query to ELasticSearch query

[Yur_Gasparyan]

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 = tags.result_id
WHERE results.id = r.id
AND published != 2
GROUP BY 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 = tags.result_id
AND activities.result = r.id
AND (r.scope = 'a2'
OR r.scope = 'a1')
GROUP BY 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

[Christian_Dahlqvist]

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.