mongodb嵌套分组和单独的数组

Question

我在下面有此示例BSON文件，我正在寻找每个交通警察的报告总数，对于每个交通警察，我想列出tpidnum，tpname和 报告总数makereport。 我正在尝试实现这样的目标：

{ "_id" : { "Name" : "Kevin Brown", ID: "KBB001"}, "Total Report" : 2},
{ "_id" : { "Name" : "Thomas Moore", ID: "TM001"}, "Total Report" : 1}

这是我到目前为止所得到的，但它一直将tpidnum和tpname这样。

{ 
  "_id" : [ "KB001", "TM001" ],
  "Total" : [ 
    { "Name" : [ "Kevin Brown", "Thomas Moore" ], 
      "ID" : [ "KB001", "TM001" ], 
      "Total Reports" : 2 
    } 
  ] 
}

db.trafficReport.aggregate({$group:{_id:"$TRAFFIC-POLICE.tpIdNum","Total":{$addToSet:{"Name": "$TRAFFIC-POLICE.tpName", "ID":"$TRAFFIC-POLICE.tpIdNum","Total Reports":{$size:"$TRAFFIC-POLICE.makeReport.reportId"}}}}})

  db.trafficReport.insert({
"TRAFFIC-POLICE": [{
    "tpIdNum": "KB001",
    "tpName": "Kevin Brown",
    "makeReport": [{
        "reportId": "TO-001",
        "reportDate": "7-October-2021",
        "hasViolation": [
            { "violationId": "VID-001" }]
    },
    {
        "reportId": "TO-002",
        "reportDate": "12-November-2021",
        "hasViolation": [
            { "violationId": "VID-002" }]
    }
    ]
},
{
    "tpIdNum": "TM001",
    "tpName": "Thomas Moore",
    "makeReport": [{
        "reportId": "TO-003",
        "reportDate": "7-October-2021",
        "hasViolation": [
            { "violationId": "VID-002" }]
    }
    ]
}],
})

Answer 1

由于流量 - Police是一个数组，因此您需要$ MAP才能格式化每个项目。 $ group如果每个警察是一个单独的文档，将有所帮助。
因此，如果您希望它们在一个文档中（如下）：

db.collection.aggregate([
  {
    $project: {
      _id: 0,
      Total: {
        $map: {
          input: "$TRAFFIC-POLICE",
          as: "pc",
          in: {
            _id: {ID: "$pc.tpIdNum", Name: "$pc.tpName"},
            "Total Report": {$size: "$pc.makeReport"}
          }
        }
      }
    }
  }
])

查看如何在

或，如果要单独的文档，请在$ $ group之前使用$ undind：

db.collection.aggregate([
  {$unwind: "$TRAFFIC-POLICE"},
  {
    $group: {
      _id: "$TRAFFIC-POLICE.tpIdNum",
      Total: {
        $addToSet: {
          Name: "$TRAFFIC-POLICE.tpName",
          ID: "$TRAFFIC-POLICE.tpIdNum"
        }
      },
      "Total Reports": {
        $first: {$size: "$TRAFFIC-POLICE.makeReport"}
      }
    }
  },
  {$unset: "_id"}
])

请参阅游乐场示例 - 单独的文档