如何用Oracle SQL生成含nodes和links的嵌套JSON对象

我需要从Oracle SQL生成能直接用于D3的JSON结构，这个结构必须包含nodes和links两个子数组。目前我已经能从数据库提取数据并生成包含nodes部分的JSON，但不知道如何把links部分整合进去，生成包含两个子对象的完整JSON。

以下是我当前可运行的简化SQL代码：

WITH NODE_TABLE AS 
(
select '1' as nodekey, '10' as nodename from dual
union
select '2' as nodekey, '20' as nodename from dual
),
LINK_TABLE AS
(
select '1' as sourcekey,'10' as sourcetarget, '100' as sourcevalue   from dual
union
select '2' as sourcekey,'20' as sourcetarget, '200' as sourcevalue   from dual
)
SELECT json_object (KEY 'nodes' value
                      (SELECT json_arrayagg (json_object ('node' value nodekey, 'name' value nodename))
                       FROM NODE_TABLE))AS mydata
FROM dual;
               
--  json_object (KEY 'links' value (
--              SELECT
--              json_arrayagg (json_object ('source' value sourcekey, 'target' value sourcetarget, 'value' value sourcevalue)) 
--               FROM
--               LINK_TABLE))AS mydata  

要整合links部分，只需在同一个json_object函数中用逗号分隔多个KEY 'xxx' value (...)结构，去掉原注释中独立的json_object语句即可。修改后的完整SQL如下：

WITH NODE_TABLE AS 
(
select '1' as nodekey, '10' as nodename from dual
union
select '2' as nodekey, '20' as nodename from dual
),
LINK_TABLE AS
(
select '1' as sourcekey,'10' as sourcetarget, '100' as sourcevalue   from dual
union
select '2' as sourcekey,'20' as sourcetarget, '200' as sourcevalue   from dual
)
SELECT json_object (KEY 'nodes' value
                      (SELECT json_arrayagg (json_object ('node' value nodekey, 'name' value nodename)) FROM NODE_TABLE),
                   KEY 'links' value 
                      (SELECT json_arrayagg (json_object ('source' value sourcekey, 'target' value sourcetarget, 'value' value sourcevalue)) FROM LINK_TABLE)                                             
                       )AS mydata
FROM dual;

核心逻辑是利用Oracle json_object支持多键值对的特性，一次性将nodes和links两个数组作为顶层键值对放入同一个JSON对象，最终输出的JSON结构完全适配D3的输入要求。

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