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:

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"}, 

CodePudding user response：

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

1. $lookup - Join both statistics and Demographic collections by country_id.
2. $set - Set death field by converting to integer.
3. $sort - Sort by death DESC.
4. $group - Group by country_id. Get first value ($first) as the result was sorted in Stage 3.
5. $sort - Sort by maxQuantity DESC.
6. $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.