mongodb MonoDB如何根据多个字段进行分组和计数

rlcwz9us 于 2022-11-22 发布在 Go

关注(0)|答案(1)|浏览(112)

提供以下格式的文件集

[
  {
    "status": "RESOLVED",
    "transactionId": "123abc",
    "associatedId": "association-1",
    "timestamp": "2022-01-01"
  },
  {
    "status": "RESOLVED",
    "transactionId": "123abc",
    "associatedId": "association-1",
    "timestamp": "2022-01-02"
  },
  {
    "status": "NOT_RESOLVED",
    "transactionId": "456xyz",
    "associatedId": "association-1",
    "timestamp": "2022-01-02"
  },
  {
    "status": "RESOLVED",
    "transactionId": "456xyz",
    "associatedId": "association-1",
    "timestamp": "2022-01-02"
  },
  {
    "status": "RESOLVED",
    "transactionId": "456xyz",
    "associatedId": "association-1",
    "timestamp": "2022-01-01"
  },
  {
    "transactionId": "456xyz",
    "associatedId": "association-1",
    "timestamp": "2022-01-01"
  },
  {
    "status": "NOT_RESOLVED",
    "transactionId": "456xyz",
    "associatedId": "association-2",
    "timestamp": "2022-01-02"
  },
  {
    "status": "RESOLVED",
    "transactionId": "456xyz",
    "associatedId": "association-2",
    "timestamp": "2022-01-02"
  },
  {
    "transactionId": "456xyz",
    "associatedId": "association-2",
    "timestamp": "2022-01-01"
  }
]

尝试根据每个associatedId查找每个transactionId的状态总数，而不考虑时间戳和总计
所以我输出应该是

| associatedId  | transactionId | RESOLVED_TOTAL | NOT_RESOLVED_TOTAL | ABSENT_TOTAL (i.e status field is absent in the document)| GRAND_TOTAL |
| association-1 | "123abc"      | 2              | 0                  | 0                                                        | 2           |
| association-1 | "456xyz"      | 2              | 1                  | 1                                                        | 4           |
| association-2 | "456xyz"      | 1              | 1                  | 1                                                        | 3           |

我尝试首先使用$project并设置状态：'ABSENT'，其中status字段对于所有文档都不存在。然后，首先基于associatedId、transactionId和status使用$group，然后使用第二个$group创建一个数组，该数组包含associatedId和status的total，但是我被卡住了。不确定是否能继续。非常感谢你的指点。

mongodb

来源：https://stackoverflow.com/questions/74476553/monodb-how-to-group-by-and-count-based-on-multiple-fields

1条答案

按热度按时间

nnt7mjpx1#

使用$ifNull捕获缺失情况（即字段缺失或字段：空值）。

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        associatedId: "$associatedId",
        transactionId: "$transactionId"
      },
      "RESOLVED_TOTAL": {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "RESOLVED",
                "$status"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "NOT_RESOLVED_TOTAL": {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "NOT_RESOLVED",
                "$status"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "ABSENT_TOTAL": {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                null,
                {
                  "$ifNull": [
                    "$status",
                    null
                  ]
                }
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "GRAND_TOTAL": {
        $sum: 1
      }
    }
  },
  {
    $sort: {
      "_id.associatedId": 1,
      "_id.transactionId": 1
    }
  }
])

Mongo Playground

赞(0）回复(0）举报 2022-11-22

我来回答

mongodb MonoDB如何根据多个字段进行分组和计数

1条答案

相关问题

热门标签

最新问答