PostgreSQL按递增整数分组生成JSONB数组的技术问询

核心思路 #

通过正则表达式去除key_path中的递增数字部分，生成统一的分组标识，再按该标识和key字段分组，最后根据分组类型（是否包含数字路径）决定聚合结果是数组还是单个值。

实现SQL #

SELECT
  -- 处理key_path_group字段：带数字路径的输出数组，无数字且单条的输出单个值
  CASE
    WHEN group_key LIKE '%[]%' THEN array_agg(key_path ORDER BY key_path)
    WHEN array_length(array_agg(key_path), 1) = 1 THEN array_agg(key_path ORDER BY key_path)[1]
    ELSE array_agg(key_path ORDER BY key_path)
  END AS key_path_group,
  key,
  -- 处理values字段：带数字路径的输出数组，无数字且单条的输出单个值
  CASE
    WHEN group_key LIKE '%[]%' THEN array_agg(value ORDER BY key_path)
    WHEN array_length(array_agg(value), 1) = 1 THEN array_agg(value ORDER BY key_path)[1]
    ELSE array_agg(value ORDER BY key_path)
  END AS values
FROM (
  -- 生成分组标识：将key_path中的[数字]替换为[]，统一同前缀的路径
  SELECT
    key_path,
    key,
    value,
    regexp_replace(key_path, '\[\d+\]', '[]', 'g') AS group_key
  FROM table_a
) t
GROUP BY group_key, key
ORDER BY key;

关键步骤说明 #

1. 生成分组标识：用regexp_replace把key_path里的[数字]替换成[]，比如foo[1]__scrog和foo[2]__scrog都会转为foo[]__scrog，确保同前缀的路径分到一组。
2. 分组聚合：按生成的group_key和key分组，用array_agg聚合key_path和value。
3. 结果格式化：通过CASE判断分组类型：
   • 如果分组标识包含[]（即原路径带递增数字），无论记录数量多少，都输出数组；
   • 如果分组标识不含[]且只有一条记录，输出单个值；多条记录则输出数组。

验证结果 #

执行上述SQL后，会得到与需求完全匹配的输出：

key_path_group                   | key     | values
------------------------------------------------------------
[foo[1]__scrog, foo[2]__scrog]   | scrog   | [apple, orange]
bar                              | bar     | peach
[baz[1]__biscuit]                | biscuit | [watermelon]

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