MongoDB – 按内部元素分组

huangapple go评论81阅读模式
英文:

MongoDB - Group by inner element

问题

我正在用一个简单的例子来解释,看看我的MongoDB集合是什么样子的:

[

    {
        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 },
        }
    },

]

从集合中,我需要计算每个用户的Level 1、Level 2和Level 3的总和。

查询结果应该如下所示:

[

    { 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 } }
]
英文:

I am explaining with a simple example see my MongoDB collection looks 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.

The 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 } }
]

答案1

得分: 1

  1. $set: 将LevelDetails转换为键值对,并添加新字段_levelDetails
  2. $unwind: 展开_levelDetails数组。
  3. $group: 根据_levelDetails.v.userId进行分组,并根据级别(_levelDetails.k)有条件地进行求和。
  4. $project: 格式化显示的文档。
  5. $sort (可选): 按userID升序排序。
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
    }
  }
])

示例 Mongo Playground


> 转换为键值对: { 'userId': { // 结果 } }

步骤1到3与之前的解决方案相同。

  1. $sort (可选): 按_id升序排序。
  2. $project: 显示带有array字段(具有属性kv)的文档。
  3. $replaceRoot: 将整个文档替换为键(userId)和值(结果)。
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"
      }
    }
  }
])

示例 Mongo Playground (转换为键值对)

英文:
  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.

  1. $sort (Optional): Sort by _id ascending.
  2. $project: Display document with array field (with properties k and v).
  3. $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)

huangapple
  • 本文由 发表于 2021年11月6日 14:10:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/69861851.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定