mongodb - 在 mongodb 中进行分组时如何使用条件计数？

Question

我需要帮助来获取条件分组中的计数。

我有以下 JSON

[
{
    "id": "103303dd56a731e377d01f6a37badae3",
    "project_id": "10006",
    "project_name": "Project_6",
    "status": "TERM"
  },
  {
    "id": "b63826f7edd2fc3ad8449add0c04fceb",
    "project_id": "10004",
    "project_name": "Project_4",
    "status": "CMP"
  },
  {
    "id": "d46e1fcf4c07ce4a69ee07e4134bcef1",
    "project_id": "10008",
    "project_name": "Project_8",
    "status": "TERM"
  },
  {
    "id": "a9fb9e6ef40426e9add520623d521ab8",
    "project_id": "10001",
    "project_name": "Project_1",
    "status": "TERM"
  },
  {
    "id": "b63826f7edd2fc3ad8449add0c04fceb",
    "project_id": "10004",
    "project_name": "Project_4",
    "status": "QF"
  }]

所以你可以看到我有重复的项目记录。我想得到这样的数据。

[
  {
    "project_id": "10007",
    "starts": 2, //Count of records where project grouped
    "Completes":3 //Where status="CMP"
    "TERMS":6 //Where status="TERM"
    "QFull":2 //Where status="QF",
    "Abandons":3 //Where status=""
  },
  {
    "project_id": "10004",
    "starts": 3, //Count of records where project grouped
    "Completes":2 //Where status="CMP"
    "TERMS":4 //Where status="TERM"
    "QFull":2 //Where status="QF",
    "Abandons":1 //Where status=""
  },
  {
    "project_id": "10001",
    "starts": 3, //Count of records where project grouped
    "Completes":2 //Where status="CMP"
    "TERMS":4 //Where status="TERM"
    "QFull":2 //Where status="QF",
    "Abandons":1 //Where status=""
  }
]

这是相同的 fiddle :https://mongoplayground.net/p/yNerdPRjbxc

到目前为止我已经尝试过:

  db.collection.aggregate([
  {
    $group: {
      _id: {
        project_id: "$project_id"
      },
      project_id: {
        $first: "$project_id"
      },
      starts: {
        $sum: 1
      }
    }
  }
])

我不确定如何根据条件在此处添加额外的字段。

Answer 1

有一个$cond可以在 $sum 中使用的运算符.因此，如果 status 符合您的条件，您只需添加 1，如果不符合，则添加 0。您可以尝试以下聚合:

db.col.aggregate([
    {
        $group: {
            _id: "$project_id",
            starts: { $sum: 1 },
            Completes: { $sum: { $cond: [ { $eq: [ "$status", "CMP" ] }, 1, 0 ] } },
            TERMS: { $sum: { $cond: [ { $eq: [ "$status", "TERM" ] }, 1, 0 ] } },
            QFull: { $sum: { $cond: [ { $eq: [ "$status", "QF" ] }, 1, 0 ] } },
            Abandons: { $sum: { $cond: [ { $eq: [ "$status", "" ] }, 1, 0 ] } },
        }
    },
    {
        $project: {
            _id: 0,
            project_id: "$_id",
            starts: 1,
            Completes: 1,
            TERMS: 1,
            QFull: 1,
            Abandons: 1
        }
    }
])

这是相同的 fiddle :https://mongoplayground.net/p/JOZJOhyrnRL

这个 fiddle 包含 $match以及如果你想检索特定项目的记录