Mongodb Mongo按最大分组排序

发布于06月12日

假设我有以下文档 struct ，其中保存了员工、他们各自的位置以及他们所在的公司:

{
    "name": "john",
    "city": "milan",
    "company": "apple"
},
{
    "name": "nicole",
    "city": "rome",
    "company": "microsoft"
}

我正在努力实现的目标，最好是在一个请求范围内，给我提供城市和公司的前n个组.我想要有他们工作的前n个城市和前n家公司.

例如:

[
    {
        "milan": 23,
        "rome": 21,
        "new york": 18,
        "berlin": 10
    },
    {
        "facebook": 10,
        "apple": 8,
        "amazon": 8,
        "google": 2
    }
]

我可以做的是使用$sum个简单的聚合管道，但这会扫描整个集合，不会给出排名靠前(最受欢迎的)结果.

推荐答案

you can use $facet in this case as mentioned in the comment by @user20042973
i used the k and v format so that I can use $arrayToObject on a later stage to convert it to an object

db.collection.aggregate([
  {
    $facet: {
      cities: [
        { $group: { _id: "$city", v: { $sum: 1 } } },
        { $sort: { v: -1 } },
        { $limit: n },
        { $project: { k: "$_id", v: "$v", _id: 0 } }
      ],
      companies: [
        { $group: { _id: "$company", v: { $sum: 1 } } },
        { $sort: { v: -1 } },
        { $limit: n },
        { $project: { k: "$_id", v: "$v", _id: 0 } }
      ]
    }
  },
  { $project: { converted: [ { $arrayToObject: "$cities" }, { $arrayToObject: "$companies" } ] } },
  { $unwind: "$converted" },
  { $replaceRoot: { newRoot: "$converted" } }
])

playground个