sql conversion to mongodb aggregate-CodePudding

I'm new to SQL and MongoDB. I'm trying to convert this:

SELECT accountType, ROUND(AVG(balance), 2) avgBalance
FROM customers
WHERE gender="female"
GROUP BY accountType
HAVING COUNT(*) < 140
ORDER BY avgBalance
LIMIT 1

to MongoDB but I can't get it to work. I don't quite understand how the order ($group, $match, $project, $round, $avg etc.) should be and how the "ROUND and AVG" are used together. This is how the answer should be like: { "accountType" : "account-type", "avgBalance" : NumberDecimal("9999.99") }

Here is what I have so far:

db.customers.aggregate( [ { $group: { _id: { accountType: "accountType", avgBalance: { $avg: { "balance" } } }, { $match: { count: { $lt: 140 } } }, { gender: "female" }, { $project: { "accountType": { $round: [ $agv: "balance", 2 ] } } }, { $limit: 1 } ] )

CodePudding user response：

Direction is not bad, would be this one:

db.customers.aggregate([
   //  WHERE gender="female"
   { $match: { gender: "female" } },
   // GROUP BY accountType, SELECT AVG(balance)
   {
      $group: {
         _id: "$accountType",
         avgBalance: { $avg: "$balance" },
         count: {$sum: 1}
      }
   },
   // HAVING COUNT(*) < 140
   { $match: { count: { $lt: 140 } } },
   // SELECT ... AS ...
   {
      $project: {
         accountType: "$_id",
         avgBalance: { $round: ["$avgBalance", 2] }
      }
   },
   // ORDER BY avgBalance
   { $sort: { avgBalance: 1 } },
   // LIMIT 1
   { $limit: 1 }
])