如何将Oracle查询的JSON_OBJECT转换为对象列表？

核心问题在于原查询是将子表数据聚合为数组后嵌套到单个JSON对象中，要实现目标输出，需要先将主表基础数据与子表的单行数据一一关联，确保每一组add/formula/code对应一条主数据副本，再对每条组合数据生成JSON对象，最后整体聚合为JSON数组。

关键步骤说明 #

1. 对齐子表行数据：由于原输出的三个数组长度一致，我们可以用ROW_NUMBER()为每个子表的行生成序号，通过序号将三个子表的行一一匹配。
2. 关联主表与子表：将主表的基础数据（name、birth等）与三个子表的行按序号关联，得到每个元素组合的行记录。
3. 生成JSON数组：对每条组合记录生成单个JSON对象，再用JSON_ARRAYAGG将所有对象聚合为最终的JSON数组。

修正后的完整SQL示例 #

假设主表间的关联逻辑已补充（原查询缺失主表关联，需根据实际业务调整），修正后的查询如下：

TO_NCLOB(
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'name' VALUE TRIM(tb1.DS_NAME),
            'birth' VALUE tb2.DT_BIRTH,
            'id' VALUE TRIM(tb2.P_ID),
            'email' VALUE tb3.DS_EMAIL,
            'phone' VALUE tb3.NUM_PHONE,
            'add' VALUE TRIM(tb4_data.ADD),
            'formula' VALUE TRIM(tb_form_data.FORM),
            'code' VALUE TRIM(tb7_data.CODE)
        )
    )
) AS USER_INFO
FROM SCHEMA1.TABLE_1 tb1
JOIN SCHEMA1.TABLE_2 tb2 ON tb1.ID = tb2.ID -- 按实际业务调整主表关联条件
JOIN SCHEMA1.TABLE_3 tb3 ON tb2.ID = tb3.ID -- 按实际业务调整主表关联条件
-- 关联带行号的地址数据
JOIN (
    SELECT 
        TRIM(tb4.ADD) AS ADD, 
        ROW_NUMBER() OVER(ORDER BY tb4.ID) AS rn
    FROM SCHEMA1.TABLE_4 tb4
    JOIN SCHEMA1.TABLE_5 tb5 ON tb4.ID_F = tb5.ID_F
    WHERE tb5.MAIN_ID = tb1.ID -- 补充主表与子表的关联条件，按需调整
) tb4_data ON 1=1
-- 关联带行号的职业数据
JOIN (
    SELECT 
        TRIM(tb4.FORM) AS FORM, 
        ROW_NUMBER() OVER(ORDER BY tb4.ID) AS rn
    FROM SCHEMA1.TABLE_4 tb4
    JOIN SCHEMA1.TABLE_5 tb5 ON tb4.ID_FORM = tb5.ID_FORM
    WHERE tb5.MAIN_ID = tb1.ID -- 补充主表与子表的关联条件，按需调整
) tb_form_data ON tb4_data.rn = tb_form_data.rn
-- 关联带行号的编码数据
JOIN (
    SELECT 
        TRIM(tb7.CODE) AS CODE, 
        ROW_NUMBER() OVER(ORDER BY tb7.ID) AS rn
    FROM SCHEMA1.TABLE_4 tb4
    JOIN SCHEMA1.TABLE_5 tb5 ON tb5.ID_U = tb4.ID
    JOIN SCHEMA1.TABLE_8 tb8 ON tb5.ID_E = tb8.ID_E
    JOIN SCHEMA1.TABLE_7 tb7 ON tb8.ID_UN = tb7.ID_P
    WHERE tb5.MAIN_ID = tb1.ID -- 补充主表与子表的关联条件，按需调整
) tb7_data ON tb4_data.rn = tb7_data.rn
GROUP BY tb1.DS_NAME, tb2.DT_BIRTH, tb2.P_ID, tb3.DS_EMAIL, tb3.NUM_PHONE;

注意事项 #

• 原查询中的子表关联存在表别名错误（如tb6未在主查询中定义），需根据实际业务表关系修正关联条件。
• 必须补充主表与子表之间的关联条件，否则会产生笛卡尔积，导致数据错误。
• ROW_NUMBER()的排序字段需根据业务逻辑调整，确保数组元素的顺序与原输出一致。

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