how to change mysql query to mongodb query

Question

I am trying to change that mysql query to mongodb format. I can't find an answer on how to do it. Please suggest a solution. I've tried several ways, but it doesn't work, please help I know I have to use a aggregate query, but it's difficult because I have a lot of subqueries. How should I configure the stage?

SELECT @rowcnt := COUNT(*) AS cnt
    , SUM(keywordcount) AS total_k_count
    , SUM(exposecount) AS total_exp, SUM(clickcount) AS total_cc
    , IF( SUM(exposecount) = 0, 0, ROUND((SUM(clickcount) / SUM(exposecount))* 100, 2) ) AS total_cr
    , IF( SUM(clickcount) = 0, 0, ROUND(SUM(cost) / SUM(clickcount)) ) AS total_ccost
    , ROUND(SUM(cost)) AS total_cost
    , SUM(COUNT) AS total_count, SUM(directcount) AS total_d_count, SUM(indirectcount) AS total_ind_count
    , IF( SUM(clickcount) = 0, 0, ROUND( ( SUM(COUNT) / SUM(clickcount) ) * 100, 2) ) AS total_con_r
    , IF( SUM(COUNT) = 0, 0, ROUND((SUM(cost) / SUM(COUNT))) ) AS total_con_expense
    , SUM(conversioncost) AS total_con_cost, SUM(conversiondirectcost) AS total_con_d_cost, SUM(conversionindirectcost) AS total_con_ind_cost
    , IF( SUM(cost) = 0, 0, ROUND( ( SUM(conversioncost) / SUM(cost) * 100 ) ) ) AS total_roas
    FROM (
        SELECT a.s_date
         , a.master_id, REPLACE( c.media_name, '\'', '\`' ) AS media_name, a.media_code
         , b.keywordcount, SUM(a.exposecount) AS exposecount, SUM(a.clickcount) AS clickcount, SUM(a.cost) AS cost
         , IF( SUM(a.exposecount) = 0, 0, ROUND((SUM(a.clickcount) / SUM(a.exposecount))* 100, 2) ) AS clickrate
         , IF( SUM(a.clickcount)= 0, 0, ROUND( (SUM(a.cost) / SUM(a.clickcount)) , 2) ) AS clickcost
         , b.count, b.directcount, b.indirectcount
         , IF( SUM(a.clickcount) = 0, 0, ROUND((b.count / SUM(a.clickcount))* 100, 2) ) AS conversionrate
         , IF( b.count = 0, 0, ROUND((SUM(a.cost) / b.count)) ) AS conversionexpense
         , b.conversioncost, b.conversiondirectcost, b.conversionindirectcost
         , IF( SUM(a.cost) = 0, 0, ROUND(( b.conversioncost / SUM(a.cost)) * 100) ) AS roas
        FROM TMP_Report AS a 
        LEFT OUTER JOIN (
            SELECT nrc.media_code, COUNT(*) AS keywordcount, nrc.master_id, SUM(nrc.count) AS COUNT, SUM(nrc.directcount) AS directcount, SUM(nrc.indirectcount) AS indirectcount
            , SUM(nrc.conversioncost) AS conversioncost, SUM(nrc.conversiondirectcost) AS conversiondirectcost, SUM(nrc.conversionindirectcost) AS conversionindirectcost
            FROM (
                SELECT nrc.media_code, nrc.s_date, nrc.master_id, nrc.keyword_id, SUM(nrc.count) AS COUNT, SUM(nrc.directcount) AS directcount, SUM(nrc.indirectcount) AS indirectcount
                , SUM(nrc.cost) AS conversioncost, SUM(nrc.directcost) AS conversiondirectcost, SUM(nrc.indirectcost) AS conversionindirectcost
                FROM  TMP_ReportConv AS nrc
                GROUP BY nrc.master_id, nrc.keyword_id, media_code, s_date
            ) AS nrc
            INNER JOIN autoanswer.calendar AS c2 ON nrc.s_date = c2.cal_date
            GROUP BY nrc.master_id, nrc.media_code
        ) AS b ON a.master_id = b.master_id AND a.media_code = b.media_code
        LEFT OUTER JOIN naver.mst_media c ON a.media_code = c.media_id
        WHERE a.s_date BETWEEN $get_s_date AND $get_e_date
        GROUP BY a.master_id, a.media_code
    ) AS m;

You may have to write an Aggregation Query. Here is some related information: SQL to Aggregation Mapping Chart. — prasad_
– prasad_, Commented Mar 29, 2022 at 6:41
Expressions like IF( SUM(clickcount) = 0, 0, ROUND(SUM(cost) / SUM(clickcount)) ) might be better written as ROUND(SUM(cost) / NULLIF(SUM(clickcount)),0) — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 29, 2022 at 7:32
Please provide some sample data and expected result. What did you try so far? — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 29, 2022 at 7:46
Usually when you migrate each table to a collection one-by-one then you have a bad design. Typically the number of collections in NoSQL database like MongoDB is much less than the number of table in according relational SQL database like MySQL. Some NoSQL databases even do not support joins at all. — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 29, 2022 at 7:47

Wernfried Domscheit · Accepted Answer · 2022-03-29 07:49:12Z

1

I am not going to do your job, but this could be a starting point:

db.TMP_Report.aggregate([
   {
      $group: {
         _id: {master_id: "$master_id", media_code: "$media_code"},
         count: { $count: {} },
         clickcount: { $sum: "$clickcount" },
         exposecount: { $sum: "$exposecount" }
      }
   },
   {
      $set: {
         total_cr: { $round: [{ $multiply: [{ $divide: ["$clickcount", {$cond: ["$exposecount", "$exposecount", null]}] }, 100] }, 2] }
      }
   }
])

answered Mar 29, 2022 at 7:49

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

han Over a year ago

Thanks for the good advice and comments

Collectives™ on Stack Overflow

how to change mysql query to mongodb query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related