mongodb - Mongo 多级聚合组

Question

在聚合展开后给出以下数据:

let workOrders = [
    {customer: 'A', job: 'Apple', chemical: {name: 'Chem A', quantity: 500}},
    {customer: 'A', job: 'Banana', chemical: {name: 'Chem B', quantity: 400}},
    {customer: 'A', job: 'Banana', chemical: {name: 'Chem C', quantity: 300}},
    {customer: 'B', job: 'Cherry', chemical: {name: 'Chem A', quantity: 200}}
]

需要的输出:

[
  {
    customer: 'A',
    jobs: [
      {
        job: 'Apple',
        chemicals: [
          {name: 'Chem A', quantity: 500}
        ]
      },
      {
        job: 'Banana',
        chemicals: [
          {name: 'Chem B', quantity: 400},
          {name: 'Chem C', quantity: 300}
        ]
      }
    ]
  },
  {
    customer: 'B',
    jobs: [
      {
        job: 'Cherry',
        chemicals: [
          {name: 'Chem A', quantity: 200}
        ]
      }
    ]
  }
]

我知道如何使用组并首先按客户分组，但后来我不明白如何在不弄乱初始客户组的情况下制作嵌套的化学品阵列。

我试过这样的东西，但它不喜欢内部的 $push。

{
“$组”:{
"_id": "$客户",
“团体”:{
$推:{
"group_data": "$客户",
"group_count": {$sum: "$customer"},
“group_child”:{
$推:{
"group_data": "$工作",
“group_count”:{$sum:“$job”}，
“group_children”:{
$推:{
"group_data": "$chemical.name",
“group_count”:{$sum:“$chemical.name”}
}
}
}
}
}
}
}


还想添加每个客户和每个工作的总数量

Answer 1

要制作嵌套化学品，您只需要两个 $group 阶段:

    db.collection.aggregate([
    {
        $group: {
            _id: { customer: "$customer", job: "$job" },
            chemicals: { $push: "$chemical" },
            jobTotal: { $sum: "$chemical.quantity" }
        }
    },
    {
        $group: {
            _id: "$_id.customer",
            jobs: { $push: { job: "$_id.job", jobTotal: "$jobTotal", chemicals: "$chemicals" } },
            customerTotal: { $sum: "$jobTotal" }
        }
    },
    {
        $project: {
            _id: 0,
            customer: "$_id.customer",
            customerTotal: 1,
            jobs: 1
        }
    }
])

Mongo Playground