I have a mongo collection with following structure:

{
  "orderID": 123456,
  "orderDate": { "$date": { "$numberLong": "1660037740974" } },
  "invoiceLines": {
    "itemLines": {
      "itemLine": [
        {
          "itemID": "id1",
          "itemName": "Item1",
          "unitCost": 500,
          "qtySold": 1,
        },
        {
          "itemID": "id2",
          "itemName": "Item2",
          "unitCost": 750,
          "qtySold": 2,
        }
      ]
    }
  },
},
{
  "orderID": 654321,
  "orderDate": { "$date": { "$numberLong": "1660037740974" } },
  "invoiceLines": {
    "itemLines": {
      "itemLine": [
        {
          "itemID": "id1",
          "itemName": "Item1",
          "unitCost": 500,
          "qtySold": 2,
        },
        {
          "itemID": "id3",
          "itemName": "Item3",
          "unitCost": 600,
          "qtySold": 1,
        }
      ]
    }
  },
},
etc.

I'm trying to find the aggregate sales quantities for each item across all orders. For example, for the above records we've sold 3 x Item1, 2 x Item2, and 1 x Item3.

I've tried to create an aggregate query for this, but it only returns the total order count for each item and not the total quantities sold across the orders.

Is this accomplished by using the aggregated methods of mongoDB? Is anyone able to assist me in building a proper query to accomplish the above?

Many thanks

CodePudding user response：

You can use $unwind and then $group to

1. split your orders in one document per order-item
2. then group them together using $sum to add the quantities up

aggregation pipeline:

db.collection.aggregate([
  {
    $unwind: "$invoiceLines.itemLines.itemLine"
  },
  {
    $group: {
      _id: "$invoiceLines.itemLines.itemLine.itemID",
      amount: {
        "$sum": "$invoiceLines.itemLines.itemLine.qtySold"
      }
    }
  }
])

Example Output for your example data:

[
  {
    "_id": "id3",
    "amount": 1
  },
  {
    "_id": "id2",
    "amount": 2
  },
  {
    "_id": "id1",
    "amount": 3
  }
]