如何在Oracle中实现与SQL Server一致的按键分组JSON输出

要实现SQL Server里FOR JSON PATH自动将[x.test1]这类层级别名解析为嵌套JSON的效果，Oracle的JSON_OBJECT需要显式构建嵌套结构，不能直接把带点的别名当成单个键处理。

静态写法示例 #

针对你给出的固定值，正确的Oracle查询需要嵌套使用JSON_OBJECT，把同一父键下的子键归到对应的子JSON对象中：

SELECT 
  (
    SELECT json_object(
             'x' VALUE json_object('test1' IS 'abc'),
             'y' VALUE json_object('test2' IS 'cde', 'test3' IS 'fgh')
           )
    FROM test
  ) AS RESULT 
FROM DUAL;

执行后会返回预期的嵌套JSON：

{"x":{"test1":"abc"},"y":{"test2":"cde","test3":"fgh"}}

动态生成SQL的处理方案 #

因为你需要从外部文件动态生成SQL，核心是对输入的每一行'值' AS [父键.子键]做预处理：

1. 拆分别名中的父键与子键：比如从[x.test1]里提取父键x和子键test1
2. 按父键分组，将同一父键下的所有子键和对应值组合成子JSON_OBJECT
3. 最后把所有父键对应的子JSON_OBJECT组合成顶层的JSON_OBJECT

例如针对你的输入内容，预处理后生成的SQL结构就是上面的静态写法，确保父键作为顶层键，对应的值是包含子键的JSON对象。

基于聚合函数的动态实现（Oracle 12cR2+） #

如果你的Oracle版本支持JSON_OBJECT_AGG，还能通过聚合方式动态构建嵌套JSON，无需手动分组：

SELECT 
  json_object_agg(parent_key VALUE json_object_agg(child_key IS val)) AS RESULT
FROM (
  SELECT 
    -- 拆分父键和子键
    regexp_substr(replace(alias_str, '[', '') , '[^.]+', 1, 1) AS parent_key,
    regexp_substr(replace(alias_str, ']', '') , '[^.]+', 1, 2) AS child_key,
    val
  FROM (
    -- 此处替换为从外部文件生成的行数据
    SELECT 'abc' AS val, 'x.test1' AS alias_str FROM test
    UNION ALL
    SELECT 'cde' AS val, 'y.test2' AS alias_str FROM test
    UNION ALL
    SELECT 'fgh' AS val, 'y.test3' AS alias_str FROM test
  )
)
GROUP BY parent_key;

这个查询会自动按父键分组，生成符合要求的嵌套JSON结构。

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