Oracle PL/SQL按分组合并多行JSON数据的实现求助

我有一张包含ID、JSON_COL、GROUP列的表，表数据如下：

ID         JSON_COL                                                   GROUP
1          { "numbers" : [ 1 , 2 ], alphabets : [ "a" , "b" ] }       1
2          { "numbers" : [ 3 , 4 ], alphabets : [ "c" , "d" ] }       1
3          { "numbers" : [ 5 , 6 ], alphabets : [ "e" , "f" ] }       2
4          { "numbers" : [ 7 , 8 ], alphabets : [ "g" , "h" ] }       2
5          { "numbers" : [ 9 , 10 ], alphabets : [ "i" , "j" ] }      2

需要按GROUP分组，将多行JSON_COL中的数据合并为单个JSON：

• 筛选GROUP=1时，期望结果：

{ "numbers" : [ 1 , 2, 3, 4 ], alphabets : [ "a" , "b" , "c" , "d" ] }

• 筛选GROUP=2时，期望结果：

{ "numbers" : [ 5 , 6 , 7 , 8 , 9 , 10 ], alphabets : [ "e" , "f" , "g" , "h" , "i" , "j" ] }

我是PL/SQL新手，尝试过JSON_TRANSFORM但仅能合并不同列，无法处理同列多行的合并，特此求助。

可以通过JSON_TABLE拆分JSON数组为关系型行数据，再按GROUP分组聚合，最后用JSON_ARRAYAGG重组数组并构造目标JSON。

通用分组合并SQL #

SELECT 
  grp AS "GROUP",
  JSON_OBJECT(
    'numbers' VALUE JSON_ARRAYAGG(num ORDER BY id),
    'alphabets' VALUE JSON_ARRAYAGG(alpha ORDER BY id)
  ) AS merged_json
FROM (
  SELECT 
    t.id,
    t."GROUP" AS grp,
    j.num,
    j.alpha
  FROM your_table t,
       JSON_TABLE(
         t.json_col,
         '$' COLUMNS (
           NESTED PATH '$.numbers[*]' COLUMNS (num NUMBER PATH '$'),
           NESTED PATH '$.alphabets[*]' COLUMNS (alpha VARCHAR2(10) PATH '$')
         )
       ) j
)
GROUP BY grp;

指定GROUP筛选的SQL #

如果只需要单个分组结果，比如筛选GROUP=1：

SELECT 
  JSON_OBJECT(
    'numbers' VALUE JSON_ARRAYAGG(num ORDER BY id),
    'alphabets' VALUE JSON_ARRAYAGG(alpha ORDER BY id)
  ) AS merged_json
FROM (
  SELECT 
    t.id,
    j.num,
    j.alpha
  FROM your_table t,
       JSON_TABLE(
         t.json_col,
         '$' COLUMNS (
           NESTED PATH '$.numbers[*]' COLUMNS (num NUMBER PATH '$'),
           NESTED PATH '$.alphabets[*]' COLUMNS (alpha VARCHAR2(10) PATH '$')
         )
       ) j
  WHERE t."GROUP" = 1
);

原理说明 #

1. JSON_TABLE：将每行JSON中的numbers和alphabets数组拆分为多行关系型数据，把数组元素转化为单独的行记录。
2. GROUP BY：按GROUP列分组，确保同一组的数据聚合在一起。
3. JSON_ARRAYAGG：将分组后的单个字段值重新聚合为JSON数组，ORDER BY id保证元素顺序与原表行顺序一致。
4. JSON_OBJECT：将两个聚合后的数组组合成最终的JSON对象，匹配目标结构。

注意：替换SQL中的your_table为实际表名；GROUP是Oracle关键字，需用双引号包裹（"GROUP"）避免语法错误。

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