由供应商名称分组

Question

我有一个带有商店订单（“从客户”和“到供应商”）的集合：

[{
  "orderNo": 1000,
  "orderItem": 0,
  "orderType": "CUST",
  "company": {
    "name": "CUSTOMER GMBH",
    "uic": "DE300000XX"
  },
  "custPO": {
    "grandTotal_eur": 146,
    "products": [
      {
        "product": {
          "code": "PROD-1000",
          "title": "Product title 1000",
          "toBuy": true,
          "supplier": {
            "code": "PROD-SUPP-1000",
            "title": "Product supplier title 1000",
            "company": {
              "name": "SUPPLIER ONE GMBH",
              "uic": "DE1000XXXX"
            },
            "price": 6
          }
        },
        "qty": 5,
        "price": 10,
        "valueTotal": 50
      },
      {
        "product": {
          "code": "PROD-2000",
          "title": "Product title 2000",
          "toBuy": true,
          "supplier": {
            "code": "PROD-SUPP-2000",
            "title": "Product supplier title 2000",
            "company": {
              "name": "SUPPLIER TWO GMBH",
              "uic": "DE2000XXXX"
            },
            "price": 15
            }
        },
        "qty": 2,
        "price": 20,
        "valueTotal": 40
      },
      {
        "product": {
            "code": "PROD-1010",
            "title": "Product title 1010",
            "toBuy": true,
            "supplier": {
              "code": "PROD-SUPP-1010",
              "title": "Product supplier title 1010",
              "company": {
                "name": "SUPPLIER ONE GMBH",
                "uic": "DE1000XXXX"
              },
              "price": 2 
            }
        },
        "qty": 2,
        "price": 3,
        "valueTotal": 6
      },
      {
        "product": {
          "code": "TRANS",
          "title": "Transport fees"
        },
        "qty": 1,
        "price": 50,
        "valueTotal": 50
      }
    ]
  }
},
{
  "orderNo": 1000,
  "orderItem": 1,
  "orderType": "SUPP",
  "company": {
    "name": "SUPPLIER ONE GMBH",
    "uic": "DE1000XXXX"
  },
  "suppPO": {
    "grandTotal_eur": 34,
    "products": [
      {
        "product": {
          "code": "PROD-SUPP-1000",
          "title": "Product supplier title 1000"
          },
        "qty": 5,
        "price": 6,
        "valueTotal": 30
      },
      {
        "product": {
            "code": "PROD-SUPP-1010",
            "title": "Product supplier title 1010"
        },
        "qty": 2,
        "price": 2,
        "valueTotal": 4
      }
    ]
  }
},
{
...
}]

总的来说，根据购物清单中产品的每个客户订单（例如1000-0）随后将其转换为多个供应商订单（例如1000-1，1000-2）。

我需要做的是创建一个mongoDB汇总，该汇总为 sum valueTotal 的所有产品标记为 tobuy ，由 >供应商（供应商由custpo.products。$。product.supplier.company.name ）

结束时，结果应该是这样的：

[{
  "orderNo": 1000,
  "company": {
    "name": "CUSTOMER GMBH",
    "uic": "DE300000XX"
  },
  "totals": [{
    "supplierName": "SUPPLIER ONE GMBH",
    "supplierTotal": 56
  },{
    "supplierName": "SUPPLIER TWO GMBH",
    "supplierTotal": 40
  },{
    "supplierName": null,
    "supplierTotal": 50
  }]
}]

可以调整输出的结构根据可能性，重要的是触摸总和名称。

如果可以用一个聚集体进行一些提示或解决方案，请为我提供一些提示或解决方案。 使用了最新版本。

Answer 1

1. $ undind - 解构 custpo.products 到多个文档的数组。

2. $ match - 用过滤文档“ custpo.products.product.tobuy”：true 。

3. $ group - 组成 orderno 和 supplierName 。

   3.1。获取公司通过 $ first 。

   3.2。 sum custpo.products.valuetotal 作为 supplototal 。

4. $ group - 组成 orderno 。

   4.1。获取公司通过 $ first 。

   4.2。用 supplierName 和 supplototal 将文档推入 totals array。

db.collection.aggregate([
  {
    $unwind: "$custPO.products"
  },
  {
    $match: {
      "custPO.products.product.toBuy": true
    }
  },
  {
    $group: {
      _id: {
        orderNo: "$orderNo",
        supplierName: "$custPO.products.product.supplier.company.name"
      },
      company: {
        $first: "$company"
      },
      "supplierTotal": {
        $sum: "$custPO.products.valueTotal"
      }
    }
  },
  {
    $group: {
      _id: "$_id.orderNo",
      company: {
        $first: "$company"
      },
      "totals": {
        $push: {
          "supplierName": "$_id.supplierName",
          "supplierTotal": "$supplierTotal"
        }
      }
    }
  }
])

示例mongo playground