时间戳

首页 > 解决方案 > MongoDB将过滤器与分组匹配并获取总数

mongodb - MongoDB将过滤器与分组匹配并获取总数

问题描述

我的样本数据：

{
    "_id": "random_id_1",
    "priority": "P1",
    "owners": ["user-1", "user-2"],
},
{
    "_id": "random_id_2",
    "priority": "P1",
    "owners": ["user-1", "user-2"],
},
{
    "_id": "random_id_3",
    "priority": "P2",
    "owners": ["user-1", "user-2"],
},

我想对涉及匹配过滤器和分组的数据运行聚合管道，我还想限制返回的组数以及每个组中的项目数。本质上，如果limit=2, limit_per_group=1, group_by=owner, priority=P1，我想要以下结果：

[
  {
     "data": [
       {
          "group_key": "user-1",
          "total_items_in_group": 2,
          "limited_items": [
             {
                 "_id": "random_id_1",
                 "priority": "P1",
                 "owners": ["user-1", "user-2"],
             },
          ],
       },
       {
          "group_key": "user-2",
          "total_items_in_group": 2,
          "limited_items": [
             {
                 "_id": "random_id_1",
                 "priority": "P1",
                 "owners": ["user-1", "user-2"],
             },
          ],
       },
     ]
  },
  {
     "metadata": {
        "total_items_matched": 2,
        "total_groups": 2
     }
  },
]

需要一些关于如何编写聚合管道以获得所需结果的帮助。我目前的查询如下：

{
    "$match": {
      "priority": "P1"
    }
  },
  {
    "$facet": {
      "data": [
        {
          $addFields: {
            "group_by_owners": "$owners"
          }
        },
        {
          $unwind: "$group_by_owners"
        },
        {
          $group: {
            "_id": "$group_by_owners",
            "total_items_in_group": {
              $sum: 1
            },
            "items": {
              $push: "$$ROOT"
            }
          }
        },
        {
          $sort: {
            "total": -1
          }
        },
        {
          $unset: "items.group_by_owners"
        },
        {
          $project: {
            "_id": 1,
            "total_items_in_group": 1,
            "limited_items": {
              $slice: [
                "$items",
                1
              ]
            }
          }
        },
        {
          "$limit": 2
        }
      ],
      "metadata": [
        {
          $count: "total_items_matched"
        }
      ]
    }
  }

Mongo playground 链接
我无法计算组的总数。

标签： mongodbmongodb-queryaggregation-frameworkdata-manipulation

解决方案

$addfields在管道末尾添加新阶段

db.collection.aggregate([
  {
    "$match": {
      "priority": "P1"
    }
  },
  {
    "$facet": {
      "data": [
        {
          $addFields: {
            "group_by_owners": "$owners"
          }
        },
        {
          $unwind: "$group_by_owners"
        },
        {
          $group: {
            "_id": "$group_by_owners",
            "total_items_in_group": {
              $sum: 1
            },
            "items": {
              $push: "$$ROOT"
            }
          }
        },
        {
          $sort: {
            "total": -1
          }
        },
        {
          $unset: "items.group_by_owners"
        },
        {
          $project: {
            "_id": 0,
            "group_key": "$_id",
            "total_items_in_group": 1,
            "limited_items": {
              $slice: [
                "$items",
                1
              ]
            }
          }
        },
        {
          "$limit": 2
        }
      ],
      "metadata": [
        {
          $count: "total_items_matched",
          
        }
      ]
    }
  },
  {
    "$addFields": {
      "metadata.total_groups": {
        "$size": "$data"
      }
    }
  }
])

https://mongoplayground.net/p/y5a0jvr6fxI

推荐阅读