MySQL query conversion to MongoDB

Question

I am new to MongoDB. I have done code to get the highest deaths value country-wise with their reporting date in MySQL. As the initial step, I am trying to get the max value of the deaths column, but it is returning another value that is not the highest. Here is my MySQL code:

SELECT 
d.country_name, s.dt, MAX(s.deaths)
FROM
Demographics d 
inner JOIN statistics s 
where d.country_id=s.country_id
GROUP BY country_name
ORDER BY MAX(s.deaths) DESC;

It is returning:


Germany	2022-01-29	118335
Bangladesh	2022-01-30	34

What will be the equivalent code to MongoDB to get the same result? To get the max value of the deaths column in MongoDB i used:

db.statistics.aggregate([
  {
    $group: {
      _id: "$country_id",
      maxQuantity: {
        $max: "$deaths"
      }
    }
  }
])

Here is my sample input:

Demographics

{"country_id":"BGD","country_name":"Bangladesh","population":"164700000","area":"148460","density":"1265"}, {"country_id":"DEU","country_name":"Germany","population":"83200000","area":"357386","density":"232"}

statistics

{"country_id":"DEU","dt":"2022-01 29", "confirmed_cases":"2016684", "deaths":"118335"},
{"country_id":"DEU","dt":"2022-01-17", "confirmed_cases":"53916", "deaths":"143"}, 
{"country_id":"BGD","dt":"2022-01-30", "confirmed_cases":"12183", "deaths":"34"},
{"country_id":"BGD","dt":"2022-01-29", "confirmed_cases":"10378", "deaths":"21"},

i want to retrieve the highest number of date with reporting date countrywise. — Eric Ipsum, Feb 16 '22 at 18:34
Please edit the question for additional information instead of using a comment — Wernfried Domscheit, Feb 16 '22 at 19:11

Yong Shun · Accepted Answer · 2022-02-17T10:42:53.607

2

Updated: Post Owner request for the max deaths for each country.

$lookup - Join both statistics and Demographic collections by country_id.
$set - Set death field by converting to integer.
$sort - Sort by death DESC.
$group - Group by country_id. Get first value ($first) as the result was sorted in Stage 3.
$sort - Sort by maxQuantity DESC.
$project - Decorate output document.

Side note: It's weird to store confirmed_cases and deaths as string type.

db.statistics.aggregate([
  {
    "$lookup": {
      "from": "Demographics",
      "localField": "country_id",
      "foreignField": "country_id",
      "as": "demographics"
    }
  },
  {
    "$set": {
      deaths: {
        $toInt: "$deaths"
      }
    }
  },
  {
    $sort: {
      deaths: -1
    }
  },
  {
    $group: {
      _id: {
        country_id: "$country_id"
      },
      country: {
        $first: "$demographics"
      },
      dt: {
        $first: "$dt"
      },
      maxQuantity: {
        $first: "$deaths"
      }
    }
  },
  {
    $sort: {
      maxQuantity: -1
    }
  },
  {
    $project: {
      _id: 0,
      country_name: {
        $first: "$country.country_name"
      },
      dt: "$dt",
      maxQuantity: "$maxQuantity"
    }
  }
])

Sample Mongo Playground

For MySQL query, INNER JOIN should be:

INNER JOIN statistics s ON d.country_id=s.country_id

and without the need of WHERE.

edited Feb 17 '22 at 10:42

answered Feb 17 '22 at 00:46

Yong Shun

35,286
4
24
46

@Young Shun thanks. But i wanted to show only the highest value of deaths of each country like this: Germany 2022-01-29 118335 Bangladesh 2022-01-30 34 . The above mongo code is showing all results in descending order.. – Eric Ipsum Feb 17 '22 at 09:34
1

Hi, I updated my answer for latest requirement. But I doubt that your MYSQL query will be fail for your new requirement. – Yong Shun Feb 17 '22 at 09:58
But my sql is giving the exact results!! why is that??!! – Eric Ipsum Feb 17 '22 at 10:06
1

Hi sorry about that. I guess due to [here](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc). After I disable the FULL_GROUP_BY in [DB-Fiddle](https://www.db-fiddle.com/f/8v4nBj92NJHPepP2uToonq/1), then it works now. – Yong Shun Feb 17 '22 at 10:14

MySQL query conversion to MongoDB

1 Answers1