Question 325374 in Mongodb Question posted in
The official documentation can be found here.

How to calculate the total value of two object arrays with the same key but different fields and combine them into one using aggregation in MongoDB?

I have data returned as an array like this:

[{
    "_id" : ObjectId("65aa4921c767f95a70b8867f"),
    "creator" : "Henry",
    "date" : "2023-12-22",
    "channels" : [
        {
            "key" : "HTV",
            "value" : 1
        }
    ]
},
{
    "_id" : ObjectId("65aa4921c767f95a70b8867f"),
    "creator" : "Max",
    "date" : "2023-12-23",
    "channels" : [
        {
            "key" : "VTC",
            "value" : 1
        }
    ]
},
{
    "_id" : ObjectId("65aa4921c767f95a70b88689"),
    "creator" : "John",
    "date" : "2023-12-23",
    "channels" : [
        {
            "key" : "VTC",
            "value" : 2
        },
        {
            "key" : "HTV",
            "value" : 1
        }
    ]
}]

I want to group by date and elements in the "channels" array with the same key will merge into one
(Total value of channels with the same key and sum of all values of channels)

I used aggregate to group "date":

model.aggregate([
      {
        $group: {
          _id: {
            date: '$date'
          },
          Total: { $sum: { $sum: '$channels.value' } }
        }
      }
    ])

But don’t know what to do next (merge duplicate keys into one, and calculate the sum of the value)

My expected results:

[{
    "date": "2023-12-22",
    "channels" : [
        {
            "key" : "HTV",
            "value" : 1
        }
    ],
    "Total" : 1
},
{
    "date": "2023-12-23",
    "channels" : [
        {
            "key" : "VTC",
            "value" : 3   // 1 + 2
        },
        {
            "key" : "HTV",
            "value" : 1
        }
    ],
    "Total" : 4
}]

Can anyone help me solve this problem? Very grateful for the responses

Tags:

2

Answers


  1. 0

    May be you shuould try to unwind the array field and perform the sum operation. Unwinding will create multiple documents with same _id fields depending on the number of array elements you have. Then may be you can perform the operation by grouping based on _id and caculate total value for particular keys.

    Login or Signup to reply.
  2. 0

    One option is:

    1. $unwind to separate the channels into documents
    2. $group by both date and key to sum the value
    3. $group by date only
    4. Format the response
    db.collection.aggregate([
  {$unwind: "$channels"},
  {$group: {
      _id: {date: "$date", key: "$channels.key"},
      value: {$sum: "$channels.value"}}
  },
  {$group: {
      _id: "$_id.date",
      channels: {$push: {key: "$_id.key", value: "$value"}},
      Total: {$sum: "$value"}
  }},
  {$set: {date: "$_id", _id: "$$REMOVE"}}
])

    See how it works on the playground example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search