Oracle 19c使用JSON函数生成无重复嵌套JSON的方案

问题背景 #

视图A的ID唯一，需关联B、C、D三个视图生成嵌套JSON结构，直接多表连接会导致JSON数组出现重复；同时要求子视图无匹配数据时，对应的JSON键不显示（如ID301无D表数据，则不出现pattern字段）。

解决方案 #

核心思路是先对子表按ID聚合生成独立的JSON数组，再与主表A左连接，最后组合成目标JSON结构，彻底避免笛卡尔积导致的重复问题。

完整SQL代码如下：

WITH A AS(
    SELECT 101 AS ID ,DATE '2024-01-03' AS dt FROM dual UNION
    SELECT 201 AS ID ,DATE '2024-03-13' AS dt FROM dual UNION 
    SELECT 301 AS ID ,DATE '2024-05-23' AS dt FROM dual 
),
B AS (
    SELECT 101 AS ID, 'ABC' AS typ, 10 AS price FROM dual UNION
    SELECT 101 AS ID, 'XYZ' AS typ, 20 AS price FROM dual UNION
    SELECT 101 AS ID, 'LMY' AS typ, 40 AS price FROM dual UNION
    SELECT 201 AS ID, 'PQR' AS typ, 30 AS price FROM dual UNION
    SELECT 301 AS ID, 'MNP' AS typ, 10 AS price FROM dual 
),
C AS (
    SELECT 101 AS ID, 'NY' AS place FROM dual UNION
    SELECT 101 AS ID, 'NJ' AS Place FROM dual UNION
    SELECT 201 AS ID, 'PA' AS Place FROM dual UNION
    SELECT 301 AS ID, 'VT' AS Place FROM dual UNION
    SELECT 301 AS ID, 'MT' AS Place FROM dual
),
D AS (
    SELECT 101 AS ID, 'BLACK' AS color FROM dual UNION
    SELECT 101 AS ID, 'WHITE' AS color FROM dual UNION
    SELECT 201 AS ID, 'PINK' AS color FROM dual UNION
    SELECT 201 AS ID, 'GREEN' AS color FROM dual
),
-- 按ID聚合生成各子表的JSON数组
agg_b AS (
    SELECT 
        ID,
        JSON_ARRAYAGG(JSON_OBJECT('typ' VALUE typ, 'price' VALUE price)) AS types
    FROM B
    GROUP BY ID
),
agg_c AS (
    SELECT 
        ID,
        JSON_ARRAYAGG(JSON_OBJECT('place' VALUE place)) AS events
    FROM C
    GROUP BY ID
),
agg_d AS (
    SELECT 
        ID,
        JSON_ARRAYAGG(JSON_OBJECT('color' VALUE color)) AS pattern
    FROM D
    GROUP BY ID
)
-- 关联主表与聚合后的子表，生成最终JSON
SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id' VALUE A.ID,
        'dt' VALUE TO_CHAR(A.dt, 'YYYY-MM-DD HH24:MI:SS'),
        -- 仅当子数组存在时才包含对应键
        CASE WHEN agg_b.types IS NOT NULL THEN 'types' VALUE agg_b.types END,
        CASE WHEN agg_c.events IS NOT NULL THEN 'events' VALUE agg_c.events END,
        CASE WHEN agg_d.pattern IS NOT NULL THEN 'pattern' VALUE agg_d.pattern END
        FORMAT JSON
    )
) AS result
FROM A
LEFT JOIN agg_b ON A.ID = agg_b.ID
LEFT JOIN agg_c ON A.ID = agg_c.ID
LEFT JOIN agg_d ON A.ID = agg_d.ID
GROUP BY A.ID, A.dt;

代码说明 #

1. 子表聚合：分别对B、C、D按ID分组，用JSON_ARRAYAGG将每组数据打包成JSON数组，从根源避免多表连接产生的笛卡尔积。
2. 主表关联：用左连接保留A的所有记录，确保即使子表无匹配数据也能保留主表信息。
3. 动态键控制：通过CASE WHEN判断子数组是否存在，仅当存在时才添加对应的JSON键，实现“无数据则不显示字段”的要求。
4. 日期格式化：用TO_CHAR将日期转换为期望的字符串格式，匹配输出要求。

输出效果 #

执行后将生成与期望完全一致的JSON结构，ID301因无D表数据，最终JSON中不会包含pattern字段。

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