mongodb - MongoDB - 使用 Group By 和 Order By 选择计数

Question

我有一个 SQL 查询，我正在尝试将其转换为 MongoDB 查询。

SELECT `CrimeLSOACode`, 
COUNT(`CrimeType`) 
AS `value_occurrence` 
FROM `Crimes` 
WHERE `CrimeType` = "Vehicle crime" 
GROUP BY `CrimeType` 
ORDER BY `value_occurrence` 
DESC LIMIT 1;

上面的查询应该返回具有最多CrimeType 设置为=“车辆犯罪”的字段的CrimeLSOACode 字段。

我曾尝试使用 SQL 到 Mongo 映射图表和转换器，但无法弄清楚如何在没有 order by 的情况下在 Mongo 中执行类似操作。

任何帮助，将不胜感激。

集合中的示例文档：

{ "_id" : ObjectId("5cdef63ce59a9d4948e81d6b"), 
"CrimeID" : "298620fed5bf614b9adef619aa7aedd24f420f7ce7cafa65f16dde13da371706", 
"CrimeMonth" : "2018-05", 
"CrimeReportedIn" : "West Yorkshire Police", 
"CrimeFallsWithin" : "West Yorkshire Police", 
"CrimeLongitude" : NumberDecimal("-1.824938"), 
"CrimeLatitude" : NumberDecimal("53.639434"), 
"CrimeLocation" : "On or near Bridge Croft", "CrimeLSOACode" : "E01011149", 
"CrimeLSOAName" : "Kirklees 045B", "CrimeType" : "Violence and sexual offences", 
"CrimeLastOutcomeCategory" : "Under investigation", 
"CrimeContext" : "" }

标签： mongodbselectcount

Answer 1

此聚合将帮助您

db.crimes.aggregate([
  { $match: { CrimeType: "Violence and sexual offences" } },
  { $group: {
      _id: "$CrimeType",
      CrimeLSOACode: { $first: "$CrimeLSOACode" },
      value_occurrence: { $sum: 1 }
    }
  },
  { $project: { _id: 0 } },
  { $sort: { value_occurrence: -1 } },
  { $limit: 1 }
])