Select Max of A field from a table in Mongo-CodePudding

I want to write the SQL statement that will select the Max of Reported and also add another column Commodity_Code then group by Commodity_Code as shown below in Mongo:

SELECT Commodity_Code as commodity_code, MAX(Reported_Date) as reported_date FROM marketstates WHERE State_Name='Gujarat'
GROUP BY Commodity_Code ORDER BY Reported_Date ASC;

I was able to write the code below in Mongo but not convinced it is what I want:

db.market_state.aggregate([{$match: {State_Name: {$eq: 'Gujarat'} }}, {$group: {_id: "$Commodity_Code", reported_date: {$max: "$Reported_Date_str"}}}, {$sort: {"Reported_Date_str": -1}} ])

Kindly point out what I am doing wrong.

CodePudding user response：

Your code works fine.

db.collection.aggregate([
  {
    $match: {
      State_Name: { $eq: "Gujarat" }
    }
  },
  {
    $group: {
      _id: "$Commodity_Code",
      reported_date: { $max: "$Reported_Date_str" }
    }
  },
  {
    $sort: { "Reported_Date_str": -1 }
  },
  {
    $set: { "commodity_Code": "$_id" }
  }
])

mongoplayground