时间戳

首页 > 解决方案 > Mongo 聚合 - 使用来自先前管道的字段值作为排序字段进行排序

mongodb - Mongo 聚合 - 使用来自先前管道的字段值作为排序字段进行排序

问题描述

我使用 mongodb 聚合（包括levelsCount字段内的 $group 管道）生成了以下输出：

{
    "_id" : "1",
    "name" : "First",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 8 }
    ]
}
{
    "_id" : "2",
    "name" : "Second",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 5 },
        { "_id" : "level_Two", "levelNum" : 2, "count" : 2 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}
{
    "_id" : "3",
    "name" : "Third",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Two", "levelNum" : 2, "count" : 3 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 2 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}

现在，我需要根据数组元素的levelNum和count字段对这些文档进行排序。levelsCount即，如果两个文档的计数均为 5 levelNum: 1 (level_One)，则排序将比较levelNum: 2 (level_Two)字段的计数，依此类推。

我看到$sort管道如何在多个字段上工作（类似的东西{ $sort : { level_One : 1, level_Two: 1 } }），但问题是如何访问levelNum每个数组元素的这些值并将该值设置为字段名称以对其进行排序。（即使在$unwindlevelsCount数组之后我也无法处理它） 。

Ps：数组元素的初始顺序levelsCount可能因每个文档而异，并不重要。

编辑： 上述结构的预期输出将是：

// Sorted result:
{
    "_id" : "2",
    "name" : "Second",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 5 }, // "level_One's count: 5"  is greater than "level_One's count: 1" in two other documents, regardless of other level_* fields. Therefore this whole document with "name: Second" is ordered first.
        { "_id" : "level_Two", "levelNum" : 2, "count" : 2 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}
{
    "_id" : "3",
    "name" : "Third",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Two", "levelNum" : 2, "count" : 3 }, // "level_Two's count" in this document exists with value (3) while the "level_Two" doesn't exist in the below document which mean (0) value for count. So this document with "name: Third" is ordered higher than the below document.
        { "_id" : "level_Three", "levelNum" : 3, "count" : 2 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 3 }
    ]
}
{
    "_id" : "1",
    "name" : "First",
    "levelsCount" : [
        { "_id" : "level_One", "levelNum" : 1, "count" : 1 },
        { "_id" : "level_Three", "levelNum" : 3, "count" : 1 },
        { "_id" : "level_Four", "levelNum" : 4, "count" : 8 }
    ]
}

当然，我更喜欢以下格式的输出文档，但第一个问题是对所有文档进行排序：

{
    "_id" : "1",
    "name" : "First",
    "levelsCount" : [
        { "level_One" : 1 },
        { "level_Three" : 1 },
        { "level_Four" : 8 }
    ]
}

标签： mongodbsortingaggregation-frameworkgrouping

解决方案

您可以按levelNum降序和count升序排序，

db.collection.aggregate([
  {
    $sort: {
      "levelsCount.levelNum": -1,
      "levelsCount.count": 1
    }
  }
])

操场

levelsCount对于数组的键值格式结果，

  • $maplevelsCount迭代数组的循环
  • 准备键值对数组并使用转换为对象$arrayToObject
  {
    $addFields: {
      levelsCount: {
        $map: {
          input: "$levelsCount",
          in: {
            $arrayToObject: [
              [{ k: "$$this._id", v: "$$this.levelNum" }]
            ]
          }
        }
      }
    }
  }

操场

推荐阅读