How to implement MYSQL union, join and left join into elasticsearch query?

Elastic Stack Elasticsearch
1

How to use mysql UNION, JOIN and LEFT JOIN in elasticsearch query?

I want to implement the following MYSQL query in elasticsearch query language.

select
   date_format(x1.week, '%Y-%m-%d') as week,
   coalesce(users, 0) as users,
   coalesce(pages, 0) as pages,
   coalesce(downloads, 0) as downloads 
from
   (
      select
         min(date(mytime)) as week,
         count(distinct uuid) as users 
      from
         (
            select
               mytime,
               uuid 
            from
               stats_view_page a 
               join
                  stats_all a2 
                  on a.payload_id = a2.id 
            where
               code = 'CS' 
               and mytime > curdate() - interval (dayofweek(curdate()) + 15) day 
            union
            select
               mytime,
               uuid 
            from
               stats_download_stop a 
               join
                  stats_all a2 
                  on a.payload_id = a2.id 
            where
               code = 'CS' 
               and mytime > curdate() - interval (dayofweek(curdate()) + 15) day x 
            group by
               if(week(mytime - interval 4 day) = 0, 52, week(mytime - interval 4 day)) limit 6 
         )
         x1 
         left join
            (
               select
                  min(date(mytime)) as week,
                  count(*) as pages 
               from
                  stats_view_page a 
                  join
                     stats_all a2 
                     on a.payload_id = a2.id 
               where
                  code = 'CS' 
                  and mytime > curdate() - interval (dayofweek(curdate()) + 15) day 
               group by
                  if(week(mytime - interval 4 day) = 0, 52, week(mytime - interval 4 day)) limit 6 
            )
            x2 
            on x1.week = x2.week 
         left join
            (
               select
                  min(date(mytime)) as week,
                  count(*) as downloads 
               from
                  stats_download_stop a 
                  join
                     stats_all a2 
                     on a.payload_id = a2.id 
               where
                  code = 'CS' 
                  and mytime > curdate() - interval (dayofweek(curdate()) + 15) day 
               group by
                  if(week(mytime - interval 4 day) = 0, 52, week(mytime - interval 4 day)) limit 6 
            )
            x3 
            on x1.week = x3.week 
      order by
         x1.week desc

Can anybody help me out ?

2

Could you format that with some code tags so we have some chance to work out what it's trying to do?

3

Hi Mark,

Thanks for the reply. I edited my post. Please have a look at it

4

Is there anybody to help me out?

5

Elasticsearch is not a relational system, and does therefore not support joins. You are therefore likely to need to store your data in a denormalized form that allows you to query it the way you want. Exactly what that would like I do not know as I do not know your data and what you want to achieve with your query.

6

Thanks for the reply Christian. I would store my data in a denormalized form and then query it to achieve what I want.

7

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