如何为SQL查询生成的JSON添加按gender分组的根元素？

问题背景 #

我编写了包含CTE的SQL查询，执行select * from output后得到包含6个产品及其属性的结果表，表结构示例如下：

|row | gender | prod_1| url_1 | prod_2 |  url_2| ...
|  1 |  male  |   nike| www.xy|  adidas| www.ap| ...
|  2 | female |   puma| www.zq|   apple| www.ad| ...

当前将该表转换为JSON后是如下数组形式：

[{
  "gender": "male",
  "product_1": "nike",
  "url_1": "www.xy ",
  "product_2": "puma",
  ...
},
{
  "gender": "female",
  "product_1": "adidas",
  "url_1": "www.xy ",
  "product_2": "apple",
  ...
}]

我希望将结果按gender分组，生成以gender为根元素的指定JSON结构，示例如下：

{
   "male": {
       "product_1": "nike",
       "url_1": "www.xy",
       "product_2": "adidas",
       ...
   },
   "female": {
       "product_1": "puma",
       "url_1": "www.zq",
       "product_2": "apple",
       ...
   }
}

请问是否可通过SQL查询实现该需求，具体方法是什么？

可以实现，具体分数据库方言处理 #

1. PostgreSQL #

PostgreSQL可以用jsonb_object_agg直接聚合键值对：

SELECT jsonb_object_agg(gender, product_data) AS result_json
FROM (
  SELECT 
    gender,
    jsonb_build_object(
      'product_1', prod_1,
      'url_1', url_1,
      'product_2', prod_2,
      'url_2', url_2
      -- 按实际表结构继续添加其他产品字段，比如product_3、url_3等
    ) AS product_data
  FROM output
) AS subquery;

• 内层用jsonb_build_object把每个gender对应的所有产品属性打包成单个JSON对象；
• 外层用jsonb_object_agg将gender作为键、对应的产品JSON作为值，聚合出目标结构。

2. MySQL 8.0+ #

MySQL 8.0及以上版本支持JSON_OBJECTAGG和JSON_OBJECT组合使用：

SELECT JSON_OBJECTAGG(gender, product_data) AS result_json
FROM (
  SELECT 
    gender,
    JSON_OBJECT(
      'product_1', prod_1,
      'url_1', url_1,
      'product_2', prod_2,
      'url_2', url_2
      -- 依次添加剩余的产品字段
    ) AS product_data
  FROM output
) AS subquery;

• JSON_OBJECT负责把单条记录的产品字段转为JSON对象；
• JSON_OBJECTAGG按gender分组，将gender作为键、产品JSON作为值聚合起来。

3. SQL Server 2016+ #

SQL Server需要用FOR JSON PATH配合字符串拼接实现：

SELECT 
  CONCAT('{', STRING_AGG(CONCAT('"', gender, '":', product_json), ','), '}') AS result_json
FROM (
  SELECT 
    gender,
    (SELECT prod_1 AS product_1, url_1, prod_2 AS product_2, url_2 
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS product_json
  FROM output
) AS subquery;

• 内层子查询用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER把每个gender的产品字段转为无数组包裹的JSON对象；
• 外层用STRING_AGG拼接每个gender的键值对字符串，再手动包裹成完整的JSON对象。

内容的提问来源于stack exchange，提问作者Thijs Kalshoven