I have a collection with store orders ("from customers" and "to suppliers") having this structure:

[{
  "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
      }
    ]
  }
},
{
...
}]

In general, each customer order (eg. 1000-0), based on the products from the shopping list, is later transposed to multiple supplier orders (eg. 1000-1, 1000-2).

What I need to do, is to create a MongoDB aggregate that output the sum of valueTotal for all products marked as toBuy, grouped by supplier (supplier is represented by custPO.products.$.product.supplier.company.name)

At the end the result should be something like this:

[{
  "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
  }]
}]

Structure of the output can be adjusted based on the possibilities, important is to touch the sum and supplier name.

Please help me with some hints or a solution, if it is possible to do it with one aggregate. Latest versions are used.

CodePudding user response：

1. $unwind - Deconstruct custPO.products array to multiple documents.

2. $match - Filter the documents with "custPO.products.product.toBuy": true.

3. $group - Group by orderNo and supplierName.

   3.1. Get company via $first.

   3.2. Sum custPO.products.valueTotal as supplierTotal.

4. $group - Group by orderNo.

   3.1. Get company via $first.

   3.2. Push the documents with supplierName and supplierTotal into 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"
        }
      }
    }
  }
])

Sample Mongo Playground