如何在Oracle的JSON_ARRAYAGG生成的JSON数组中添加虚拟记录

我来帮你搞定这个需求！要在生成的JSON数组里加入那条虚拟记录，核心思路是先把原表的有效记录和虚拟记录合并，再进行JSON聚合操作。下面是具体的实现方案：

通用解决方案（字符串类型code） #

如果你的code字段是字符串类型，直接用UNION ALL把虚拟行和原查询结果合并即可：

select json_object( 
    key 'list' VALUE ( 
        select json_arrayagg( 
            json_object( 
                key 'code' VALUE code, 
                key 'name' VALUE name 
            ) ORDER BY code ASC 
        ) 
        from (
            -- 原表中符合条件的记录
            select ct.code, ct.name 
            from CASINO.CLIENTTYPES ct 
            WHERE ct.code != 'system'
            -- 追加虚拟记录
            UNION ALL
            select 'dummy code' as code, 'dummy name' as name from DUAL
        ) combined_records
    ) returning clob 
) responseJson 
FROM DUAL

数值类型code适配方案 #

如果你的code是数值类型（比如示例里的1、2），记得把虚拟记录的code改成对应数值，比如用一个不会和现有数据冲突的数值（比如999）：

select json_object( 
    key 'list' VALUE ( 
        select json_arrayagg( 
            json_object( 
                key 'code' VALUE code, 
                key 'name' VALUE name 
            ) ORDER BY code ASC 
        ) 
        from (
            select ct.code, ct.name 
            from CASINO.CLIENTTYPES ct 
            WHERE ct.code != 'system'
            UNION ALL
            select 999 as code, 'dummy name' as name from DUAL
        ) combined_records
    ) returning clob 
) responseJson 
FROM DUAL

额外优化：固定虚拟记录在数组末尾 #

如果希望虚拟记录始终排在数组最后，不受code排序影响，可以给合并后的记录加个排序权重字段：

select json_object( 
    key 'list' VALUE ( 
        select json_arrayagg( 
            json_object( 
                key 'code' VALUE code, 
                key 'name' VALUE name 
            ) ORDER BY sort_order ASC, code ASC 
        ) 
        from (
            select ct.code, ct.name, 1 as sort_order
            from CASINO.CLIENTTYPES ct 
            WHERE ct.code != 'system'
            UNION ALL
            select 'dummy code' as code, 'dummy name' as name, 2 as sort_order from DUAL
        ) combined_records
    ) returning clob 
) responseJson 
FROM DUAL

这样原表记录会优先按code排序，虚拟记录会固定排在数组最后。

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