使用子查询拼接多列为JSON对象：Oracle父子表关联查询需求

需求 #

在DBeaver连接的Oracle环境中，关联父表与子表，将每个父表记录对应的所有子表数据合并为单个JSON数组格式的列值。

示例数据 #

父表（Parent） #

子表（Child） #

关联规则：Child.REL_ID = Parent.ID

解决方案SQL #

SELECT
    p.ID,
    -- 如需大写父表名称可替换为 UPPER(p.NAME)
    p.NAME,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'REL_ID' VALUE c.REL_ID,
            -- 如需大写子表名称可替换为 UPPER(c.NAME)
            'NAME' VALUE c.NAME,
            'AGE' VALUE c.AGE
        ) FORMAT JSON
    ) AS CHILD
FROM Parent p
LEFT JOIN Child c ON p.ID = c.REL_ID
GROUP BY p.ID, p.NAME
ORDER BY p.ID;

关键函数说明 #

• JSON_OBJECT：将子表指定字段封装为单个JSON对象，对应结果数组中的每个元素。
• JSON_ARRAYAGG：按父表分组，将同组内的子表JSON对象聚合为一个数组，作为CHILD列的值。
• LEFT JOIN：保证无对应子数据的父表记录也能被保留，此时CHILD列返回空数组[]。

执行结果 #

注：若需要和预期结果一样将名称转为大写，只需将SQL中对应的NAME字段替换为UPPER(p.NAME)和UPPER(c.NAME)即可。

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