MongoDB aggregate and summary result using $match and $group

Question

So I have listed an array of objects from the MongoDB document below:

data=[
{
  "_id": {
    "$oid": "63f5e2449741a13be769ca16"
  },
  "userId": {
    "$oid": "63f5bf0c0dc9b0c36ff32087"
  },
  "Jumlah": 12600,
  "Batch": "9999999",
  "Material":"2123100085",
  "Expired_Date": "2099-99-99",
  "Gedung": "C",
  "Zona": "STAGING INBOUND",
  "Plant": "K118",
  "Transaction_Status": "verified",
  "Transaction_Date": "2023-02-22",
  "createdAt": 1677058628,
  "updatedAt": 1677058628,
  "__v": 0,
  "From": "INBOUND BONGKAR"
},{
  "_id": {
    "$oid": "63f5e2449741a13be769ca16"
  },
  "userId": {
    "$oid": "63f5bf0c0dc9b0c36ff32087"
  },
  "Material":"2123100085",
  "Jumlah": 600,
  "Batch": "9999999",
  "Expired_Date": "2099-99-99",
  "Gedung": "C",
  "Zona": "STAGING INBOUND",
  "Plant": "K118",
  "Transaction_Status": "verified",
  "Transaction_Date": "2023-02-22",
  "createdAt": 1677058628,
  "updatedAt": 1677058628,
  "__v": 0,
  "From": "INBOUND BONGKAR"
},{
  "_id": {
    "$oid": "63f5e2449741a13be769ca16"
  },
  "userId": {
    "$oid": "63f5bf0c0dc9b0c36ff32087"
  },
  "Jumlah": 12100,
  "Batch": "9999999", 
  "Material":"2123100085",
  "Expired_Date": "2099-99-99",
  "Gedung": "C",
  "Zona": "STAGING INBOUND",
  "Plant": "K118",
  "Transaction_Status": "verified",
  "Transaction_Date": "2023-02-23",
  "createdAt": 1677058628,
  "updatedAt": 1677058628,
  "__v": 0,
  "From": "INBOUND BONGKAR"
}
]

I try to group it by date using MongoDB query with the result below:

{
    "status": 200,
    "message": "Success",
    "data": [
        {
            "_id": "2022400045",
            "data": [
                {
                    "x": "2023-02-22",
                    "y": 20400
                },
                {
                    "x": "2023-02-25",
                    "y": 20000
                },
                {
                    "x": "2023-02-25",
                    "y": 9000
                },
                {
                    "x": "2023-02-25",
                    "y": 4000
                },
                {
                    "x": "2023-02-25",
                    "y": 7000
                },
                {
                    "x": "2023-02-25",
                    "y": 3000
                },
                {
                    "x": "2023-02-25",
                    "y": 3000
                }
            ]
        },
]
}

here is my try:

const result = await TransactionIB.aggregate([
        { $match: { Transaction_Date: { $gte: date1, $lte: date2 } } },
        {
          $group: {
            _id: "$Material",
            Material_Description: { $first: "$Material_Description" },
            data: {
              $push: {
                x: "$Transaction_Date",
                y: { $sum: "$Jumlah" },
              },
            },
          },
        },
      ]);

is that a way to optimize the query so the expected value will be grouped not only by the material but also by the date with the expected result as below, or any help on this?:

"data": [
        {
            "_id": "2022400045",
            "data": [
                {
                    "x": "2023-02-22",
                    "y": 20400
                },
                {
                    "x": "2023-02-25",
                    "y": 500000
                },
            ]
        },
]

Answer 1

It requires two levels of groups,

• $group by Material and Transaction_Date and get a total of Jumlah in y
• $group by only Material and construct the data array

db.collection.aggregate([
  {
    $group: {
      _id: {
        Material: "$Material",
        Transaction_Date: "$Transaction_Date"
      },
      Material_Description: {
        $first: "$Material_Description"
      },
      y: {
        $sum: "$Jumlah"
      }
    }
  },
  {
    $group: {
      _id: "$_id.Material",
      Material_Description: {
        $first: "$Material_Description"
      },
      data: {
        $push: {
          x: "$_id.Transaction_Date",
          y: "$y"
        }
      }
    }
  }
])

Playground