MongoDB 数据库中某 collection(名为 product_category) 数据如下：

[
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 1234,
    "category": "drink"
  },
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 2122,
    "category": "soda"
  },
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 2121,
    "category": "mocktail"
  },
  {
    "product_id": 1212,
    "name": "sprit",
    "category_id": 2121,
    "category": "mocktail"
  }
]

要求：找到该产品不重复的分类，变成多层结构，其中，俩产品字段位于上层，俩分类字段位于下层数组。期望结果如下：

[
  {
    "name": "sprit",
    "product_id": 1212,
    "categogies": [
      {
        "category": "soda",
        "category_id": 2122
      },
      {
        "category": "drink",
        "category_id": 1234
      },
      {
        "category": "mocktail",
        "category_id": 2121
      }
    ]
  }
]

mongodb query 本身的语法比较长，很简单的分组也要写很多行:

db.product_category.aggregate([
  {
    $group: {
      _id: "$product_id",
      name: {
        $first: "$name"
      },
      categogies: {
        $addToSet: {
          "category_id": "$category_id",
          "category": "$category"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      product_id: "$_id",
      name: "$name",
      categogies: "$categogies"
    }
  }
])

SPL 的分组可以保留分组子集，一行代码即可搞定，而且易读易懂：

	A
1	=mongo_open@d("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'product_category','projection':{'_id':0}}")
3	=mongo_close(A1)
4	=A2.group(product_id;~.name,~.groups(category_id;category):categories)
5	=json(A4)

前三行读数，第四行计算，第五行将结果转回 json 格式

问题来源：https://stackoverflow.com/questions/78447108/mongodb-convert-response-into-array-of-object-in-query