I am explaining with a simple example see my mongodb collection look like this, [

    {
        pid: erwer,
        qty: 3,
        LevelDetails: {
            level1: { userId: 1, amount: 10 },
            level2: { userId: 2, amount: 20 },
            level3: { userId: 3, amount: 13 },
        }
    },
    {
        pid: qwsdfg,
        qty: 1,
        LevelDetails: {
            level1: { userId: 1, amount: 10 },
            level2: { userId: 4, amount: 20 },
            level3: { userId: 3, amount: 13 },
        }
    },

]

From the collection, I need the sum of Level 1, Level 2 and Level 3 for each user. query result should look like this [

        { userId1: { TotalLevel1Amount: 20, TotalLevel2Amount: 0, TotalLevel3Amount: 0 } },

        { userId2: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } },

        { userId3: { TotalLevel1Amount: 0, TotalLevel2Amount: 0, TotalLevel3Amount: 26 } },

        { userId4: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } }
    ]

CodePudding user response：

1. $set: Add new field _levelDetails by converting LevelDetails to key-value pair.
2. $unwind: Deconstruct _levelDetails array.
3. $group: Group by _levelDetails.v.userId and $sum conditionally based on level (_levelDetails.k).
4. $project: Format displayed document.
5. $sort (Optional): Sort by userID ascending.

db.collection.aggregate([
  {
    $set: {
      _levelDetails: {
        $objectToArray: "$LevelDetails"
      }
    }
  },
  {
    $unwind: "$_levelDetails"
  },
  {
    $group: {
      _id: "$_levelDetails.v.userId",
      "TotalLevel1Amount": {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$_levelDetails.k",
                "level1"
              ]
            },
            "$_levelDetails.v.amount",
            0
          ]
        }
      },
      "TotalLevel2Amount": {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$_levelDetails.k",
                "level2"
              ]
            },
            "$_levelDetails.v.amount",
            0
          ]
        }
      },
      "TotalLevel3Amount": {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$_levelDetails.k",
                "level3"
              ]
            },
            "$_levelDetails.v.amount",
            0
          ]
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      userId: "$_id",
      TotalLevel1Amount: 1,
      TotalLevel2Amount: 1,
      TotalLevel3Amount: 1
    }
  },
  {
    $sort: {
      userId: 1
    }
  }
])

Sample Mongo Playground

To Key-Value Pair: { 'userId': { // Result } }

Steps 1 to 3 are the same as the previous solution.

4. $sort (Optional): Sort by _id ascending.
5. $project: Display document with array field (with properties k and v).
6. $replaceRoot: Replace entire documents to key (userId) and value (result).

db.collection.aggregate([
  {
    $set: {
      _levelDetails: {
        $objectToArray: "$LevelDetails"
      }
    }
  },
  {
    $unwind: "$_levelDetails"
  },
  {
    $group: {
      _id: "$_levelDetails.v.userId",
      "TotalLevel1Amount": {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$_levelDetails.k",
                "level1"
              ]
            },
            "$_levelDetails.v.amount",
            0
          ]
        }
      },
      "TotalLevel2Amount": {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$_levelDetails.k",
                "level2"
              ]
            },
            "$_levelDetails.v.amount",
            0
          ]
        }
      },
      "TotalLevel3Amount": {
        $sum: {
          $cond: [
            {
              "$eq": [
                "$_levelDetails.k",
                "level3"
              ]
            },
            "$_levelDetails.v.amount",
            0
          ]
        }
      }
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $project: {
      array: [
        {
          k: {
            $toString: "$_id"
          },
          v: {
            TotalLevel1Amount: "$TotalLevel1Amount",
            TotalLevel2Amount: "$TotalLevel2Amount",
            TotalLevel3Amount: "$TotalLevel3Amount"
          }
        }
      ]
    }
  },
  {
    "$replaceRoot": {
      newRoot: {
        $arrayToObject: "$array"
      }
    }
  }
])

Sample Mongo Playground (To Key-Value Pair)