如何将指定关联分组统计SQL语句转换为MongoDB查询？

原SQL逻辑是关联region、company两张表，按地区ID匹配关联后，按地区名称分组统计对应公司数量，按统计值降序取前5条结果，对应MongoDB实现如下：

实现方式1（从region集合出发，逻辑更直观） #

适合数据量较小的场景，代码逻辑和原SQL对应关系更清晰：

db.region.aggregate([
  // 关联company集合，匹配RegionID与region的_id
  {
    $lookup: {
      from: "company",
      localField: "_id",
      foreignField: "RegionID",
      as: "related_companies"
    }
  },
  // 统计每个地区的关联公司数量
  {
    $addFields: {
      companyCount: { $size: "$related_companies" }
    }
  },
  // 按公司数量降序排序
  {
    $sort: { companyCount: -1 }
  },
  // 取前5条结果
  {
    $limit: 5
  },
  // 仅返回需要的字段，和原SQL输出一致
  {
    $project: {
      _id: 0,
      regionName: 1,
      companyCount: 1
    }
  }
])

实现方式2（从company集合出发，性能更优） #

适合公司数据量级较大的场景，先分组统计再关联，减少关联时的计算量：

db.company.aggregate([
  // 先按RegionID分组统计各地区的公司数量
  {
    $group: {
      _id: "$RegionID",
      companyCount: { $count: {} }
    }
  },
  // 关联region集合获取地区名称
  {
    $lookup: {
      from: "region",
      localField: "_id",
      foreignField: "_id",
      as: "regionInfo"
    }
  },
  // 拆分关联返回的数组结构
  {
    $unwind: "$regionInfo"
  },
  // 格式化输出字段
  {
    $project: {
      _id: 0,
      regionName: "$regionInfo.regionName",
      companyCount: 1
    }
  },
  // 降序排序取前5
  {
    $sort: { companyCount: -1 }
  },
  {
    $limit: 5
  }
])

注意事项 #

• 两种实现返回的结果结构完全和原SQL要求一致，regionName为地区名称，companyCount为对应地区的公司数量
• 第二种实现优先对company做分组聚合，大幅减少后续关联操作的计算量，生产环境更推荐使用

内容的提问来源于stack exchange，提问作者m 22