MongoDB – 按内部元素分组

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

MongoDB - Group by inner element

问题

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

  1. [
  3.     {
  4.         pid: erwer,
  5.         qty: 3,
  6.         LevelDetails: {
  7.             level1: { userId: 1, amount: 10 },
  8.             level2: { userId: 2, amount: 20 },
  9.             level3: { userId: 3, amount: 13 },
  10.         }
  11.     },
  12.     {
  13.         pid: qwsdfg,
  14.         qty: 1,
  15.         LevelDetails: {
  16.             level1: { userId: 1, amount: 10 },
  17.             level2: { userId: 4, amount: 20 },
  18.             level3: { userId: 3, amount: 13 },
  19.         }
  20.     },
  22. ]

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

查询结果应该如下所示：

  1. [
  3.     { userId1: { TotalLevel1Amount: 20, TotalLevel2Amount: 0, TotalLevel3Amount: 0 } },
  5.     { userId2: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } },
  7.     { userId3: { TotalLevel1Amount: 0, TotalLevel2Amount: 0, TotalLevel3Amount: 26 } },
  9.     { userId4: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } }
  10. ]
英文:

I am explaining with a simple example see my MongoDB collection looks like this:

  1. [
  3.     {
  4.         pid: erwer,
  5.         qty: 3,
  6.         LevelDetails: {
  7.             level1: { userId: 1, amount: 10 },
  8.             level2: { userId: 2, amount: 20 },
  9.             level3: { userId: 3, amount: 13 },
  10.         }
  11.     },
  12.     {
  13.         pid: qwsdfg,
  14.         qty: 1,
  15.         LevelDetails: {
  16.             level1: { userId: 1, amount: 10 },
  17.             level2: { userId: 4, amount: 20 },
  18.             level3: { userId: 3, amount: 13 },
  19.         }
  20.     },
  22. ]

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:

  1. [
  3.     { userId1: { TotalLevel1Amount: 20, TotalLevel2Amount: 0, TotalLevel3Amount: 0 } },
  5.     { userId2: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } },
  7.     { userId3: { TotalLevel1Amount: 0, TotalLevel2Amount: 0, TotalLevel3Amount: 26 } },
  9.     { userId4: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } }
  10. ]

答案1

得分: 1

  1. $set: 将LevelDetails转换为键值对，并添加新字段_levelDetails。
  2. $unwind: 展开_levelDetails数组。
  3. $group: 根据_levelDetails.v.userId进行分组，并根据级别(_levelDetails.k)有条件地进行求和。
  4. $project: 格式化显示的文档。
  5. $sort (可选): 按userID升序排序。
  1. db.collection.aggregate([
  2.   {
  3.     $set: {
  4.       _levelDetails: {
  5.         $objectToArray: "$LevelDetails"
  6.       }
  7.     }
  8.   },
  9.   {
  10.     $unwind: "$_levelDetails"
  11.   },
  12.   {
  13.     $group: {
  14.       _id: "$_levelDetails.v.userId",
  15.       "TotalLevel1Amount": {
  16.         $sum: {
  17.           $cond: [
  18.             {
  19.               "$eq": [
  20.                 "$_levelDetails.k",
  21.                 "level1"
  22.               ]
  23.             },
  24.             "$_levelDetails.v.amount",
  25.             0
  26.           ]
  27.         }
  28.       },
  29.       "TotalLevel2Amount": {
  30.         $sum: {
  31.           $cond: [
  32.             {
  33.               "$eq": [
  34.                 "$_levelDetails.k",
  35.                 "level2"
  36.               ]
  37.             },
  38.             "$_levelDetails.v.amount",
  39.             0
  40.           ]
  41.         }
  42.       },
  43.       "TotalLevel3Amount": {
  44.         $sum: {
  45.           $cond: [
  46.             {
  47.               "$eq": [
  48.                 "$_levelDetails.k",
  49.                 "level3"
  50.               ]
  51.             },
  52.             "$_levelDetails.v.amount",
  53.             0
  54.           ]
  55.         }
  56.       }
  57.     }
  58.   },
  59.   {
  60.     $project: {
  61.       _id: 0,
  62.       userId: "$_id",
  63.       TotalLevel1Amount: 1,
  64.       TotalLevel2Amount: 1,
  65.       TotalLevel3Amount: 1
  66.     }
  67.   },
  68.   {
  69.     $sort: {
  70.       userId: 1
  71.     }
  72.   }
  73. ])

示例 Mongo Playground

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

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

  1. $sort (可选): 按_id升序排序。
  2. $project: 显示带有array字段（具有属性k和v）的文档。
  3. $replaceRoot: 将整个文档替换为键(userId)和值（结果）。
  1. db.collection.aggregate([
  2.   {
  3.     $set: {
  4.       _levelDetails: {
  5.         $objectToArray: "$LevelDetails"
  6.       }
  7.     }
  8.   },
  9.   {
  10.     $unwind: "$_levelDetails"
  11.   },
  12.   {
  13.     $group: {
  14.       _id: "$_levelDetails.v.userId",
  15.       "TotalLevel1Amount": {
  16.         $sum: {
  17.           $cond: [
  18.             {
  19.               "$eq": [
  20.                 "$_levelDetails.k",
  21.                 "level1"
  22.               ]
  23.             },
  24.             "$_levelDetails.v.amount",
  25.             0
  26.           ]
  27.         }
  28.       },
  29.       "TotalLevel2Amount": {
  30.         $sum: {
  31.           $cond: [
  32.             {
  33.               "$eq": [
  34.                 "$_levelDetails.k",
  35.                 "level2"
  36.               ]
  37.             },
  38.             "$_levelDetails.v.amount",
  39.             0
  40.           ]
  41.         }
  42.       },
  43.       "TotalLevel3Amount": {
  44.         $sum: {
  45.           $cond: [
  46.             {
  47.               "$eq": [
  48.                 "$_levelDetails.k",
  49.                 "level3"
  50.               ]
  51.             },
  52.             "$_levelDetails.v.amount",
  53.             0
  54.           ]
  55.         }
  56.       }
  57.     }
  58.   },
  59.   {
  60.     $sort: {
  61.       _id: 1
  62.     }
  63.   },
  64.   {
  65.     $project: {
  66.       array: [
  67.         {
  68.           k: {
  69.             $toString: "$_id"
  70.           },
  71.           v: {
  72.             TotalLevel1Amount: "$TotalLevel1Amount",
  73.             TotalLevel2Amount: "$TotalLevel2Amount",
  74.             TotalLevel3Amount: "$TotalLevel3Amount"
  75.           }
  76.         }
  77.       ]
  78.     }
  79.   },
  80.   {
  81.     "$replaceRoot": {
  82.       newRoot: {
  83.         $arrayToObject: "$array"
  84.       }
  85.     }
  86.   }
  87. ])

示例 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.
  1. db.collection.aggregate([
  2.   {
  3.     $set: {
  4.       _levelDetails: {
  5.         $objectToArray: "$LevelDetails"
  6.       }
  7.     }
  8.   },
  9.   {
  10.     $unwind: "$_levelDetails"
  11.   },
  12.   {
  13.     $group: {
  14.       _id: "$_levelDetails.v.userId",
  15.       "TotalLevel1Amount": {
  16.         $sum: {
  17.           $cond: [
  18.             {
  19.               "$eq": [
  20.                 "$_levelDetails.k",
  21.                 "level1"
  22.               ]
  23.             },
  24.             "$_levelDetails.v.amount",
  25.             0
  26.           ]
  27.         }
  28.       },
  29.       "TotalLevel2Amount": {
  30.         $sum: {
  31.           $cond: [
  32.             {
  33.               "$eq": [
  34.                 "$_levelDetails.k",
  35.                 "level2"
  36.               ]
  37.             },
  38.             "$_levelDetails.v.amount",
  39.             0
  40.           ]
  41.         }
  42.       },
  43.       "TotalLevel3Amount": {
  44.         $sum: {
  45.           $cond: [
  46.             {
  47.               "$eq": [
  48.                 "$_levelDetails.k",
  49.                 "level3"
  50.               ]
  51.             },
  52.             "$_levelDetails.v.amount",
  53.             0
  54.           ]
  55.         }
  56.       }
  57.     }
  58.   },
  59.   {
  60.     $project: {
  61.       _id: 0,
  62.       userId: "$_id",
  63.       TotalLevel1Amount: 1,
  64.       TotalLevel2Amount: 1,
  65.       TotalLevel3Amount: 1
  66.     }
  67.   },
  68.   {
  69.     $sort: {
  70.       userId: 1
  71.     }
  72.   }
  73. ])

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).
  1. db.collection.aggregate([
  2.   {
  3.     $set: {
  4.       _levelDetails: {
  5.         $objectToArray: "$LevelDetails"
  6.       }
  7.     }
  8.   },
  9.   {
  10.     $unwind: "$_levelDetails"
  11.   },
  12.   {
  13.     $group: {
  14.       _id: "$_levelDetails.v.userId",
  15.       "TotalLevel1Amount": {
  16.         $sum: {
  17.           $cond: [
  18.             {
  19.               "$eq": [
  20.                 "$_levelDetails.k",
  21.                 "level1"
  22.               ]
  23.             },
  24.             "$_levelDetails.v.amount",
  25.             0
  26.           ]
  27.         }
  28.       },
  29.       "TotalLevel2Amount": {
  30.         $sum: {
  31.           $cond: [
  32.             {
  33.               "$eq": [
  34.                 "$_levelDetails.k",
  35.                 "level2"
  36.               ]
  37.             },
  38.             "$_levelDetails.v.amount",
  39.             0
  40.           ]
  41.         }
  42.       },
  43.       "TotalLevel3Amount": {
  44.         $sum: {
  45.           $cond: [
  46.             {
  47.               "$eq": [
  48.                 "$_levelDetails.k",
  49.                 "level3"
  50.               ]
  51.             },
  52.             "$_levelDetails.v.amount",
  53.             0
  54.           ]
  55.         }
  56.       }
  57.     }
  58.   },
  59.   {
  60.     $sort: {
  61.       _id: 1
  62.     }
  63.   },
  64.   {
  65.     $project: {
  66.       array: [
  67.         {
  68.           k: {
  69.             $toString: "$_id"
  70.           },
  71.           v: {
  72.             TotalLevel1Amount: "$TotalLevel1Amount",
  73.             TotalLevel2Amount: "$TotalLevel2Amount",
  74.             TotalLevel3Amount: "$TotalLevel3Amount"
  75.           }
  76.         }
  77.       ]
  78.     }
  79.   },
  80.   {
  81.     "$replaceRoot": {
  82.       newRoot: {
  83.         $arrayToObject: "$array"
  84.       }
  85.     }
  86.   }
  87. ])

Sample Mongo Playground (To Key-Value Pair)

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

发表评论

匿名网友

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

确定