I'm having documents that are having this structures

x = {
       "scalar": 1,
       "array": [
          {"key": 1, "value": 2},
          {"key": 2, "value": 3},
       ],
       "array2": [
          {"key": 1, "value": 2},
          {"key": 2, "value": 3},
       ],
    }

and

y = {
    "scalar": 2,
    "array": [
        {"key": 1, "value": 3},
        {"key": 3, "value": 0},
    ],
    "array2": [
        {"key": 1, "value": 3},
        {"key": 3, "value": 0},
    ],
}

The end results I'm trying to find is this

{
    "scalar": 3, # SUM of scalar
    "array": [
        {"key": 1, "value": 5},  # SUM by key = 1
        {"key": 2, "value": 3},
        {"key": 3, "value": 0},
    ],
    "array2": [
        {"key": 1, "value": 5},  # SUM by key = 1
        {"key": 2, "value": 3},
        {"key": 3, "value": 0},
    ],
}

I've tried to use double $unwind and then do push by. I'm thinking of using $reduce to get the final results

CodePudding user response：

Query

• one way to do it, is by facet, you want 3 groupings and facet can do that , like break into 3 seperate parts, to not mix the unwinds, i think this is the most simple way to do it

Test code here

db.collection.aggregate([
  {
    "$facet": {
      "scalar": [
        {
          "$project": {
            "scalar": 1
          }
        },
        {
          "$group": {
            "_id": null,
            "sum": {
              "$sum": "$scalar"
            }
          }
        },
        {
          "$unset": [
            "_id"
          ]
        }
      ],
      "array": [
        {
          "$project": {
            "array": 1
          }
        },
        {
          "$unwind": {
            "path": "$array"
          }
        },
        {
          "$group": {
            "_id": "$array.key",
            "sum": {
              "$sum": "$array.value"
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            "key": "$_id",
            "value": "$sum"
          }
        }
      ],
      "array2": [
        {
          "$project": {
            "array2": 1
          }
        },
        {
          "$unwind": {
            "path": "$array2"
          }
        },
        {
          "$group": {
            "_id": "$array2.key",
            "sum": {
              "$sum": "$array2.value"
            }
          }
        },
        {
          "$project": {
            "_id": 0,
            "key": "$_id",
            "value": "$sum"
          }
        }
      ]
    }
  },
  {
    "$set": {
      "scalar": {
        "$arrayElemAt": [
          "$scalar.sum",
          0
        ]
      }
    }
  }
])

Other alternative is to unwind both arrays, but then unwinds and groups will be mixed, making things complicated i think.

Also $reduce cant be used for grouping in MongoDB i think, because we can't construct dynamic paths.

If group-reduce and have this data (key=key value=value)

{"1" : 5 , "2" : 3}

And we see {"key" 1, "value" : 5} how we can check if the above data contains the 1 as key? We cant construct dynamic paths, like $$this.1 . Only way it to convert it to an array and back to object that will be so slow.