Oracle 21 XE中如何用JSON_OBJECT生成1:n关系的合并JSON

在Oracle 21 XE环境中，已创建一对1:n关联的parent表和child表，表结构及测试数据如下：

CREATE TABLE parent (
    id integer NOT NULL,
    last_name varchar(50) NOT NULL,
    CONSTRAINT parent_pkey PRIMARY KEY (id)
);

CREATE TABLE child (
    id integer NOT NULL,
    parent_id integer NOT NULL,
    name varchar(50) NOT NULL,
    CONSTRAINT child_pkey PRIMARY KEY (id),
    foreign key (parent_id) references parent(id)
);

insert into parent (id, last_name) values (1, 'Mom');
insert into child (id, parent_id, name) values (1, 1, 'Kid 1');
insert into child (id, parent_id, name) values (2, 1, 'Kid 2');
insert into child (id, parent_id, name) values (3, 1, 'Kid 3');

执行以下SQL时：

SELECT JSON_OBJECT(parent.*, 'children' value json_array(json_object (child.*))) 
FROM Parent, Child 
WHERE child.parent_id = parent.id and parent.id = 1;

会得到3条独立的JSON结果，每条的children数组仅包含一个子项。期望生成单条JSON，其中children数组包含所有关联的子表数据，格式如下：

{"ID":1,"LAST_NAME":"Mom","children":[{"ID":1,"PARENT_ID":1,"NAME":"Kid 1"},
                                      {"ID":2,"PARENT_ID":1,"NAME":"Kid 2"},
                                      {"ID":3,"PARENT_ID":1,"NAME":"Kid 3"}]}

使用Oracle的JSON_ARRAYAGG函数替代JSON_ARRAY，该函数可将多行JSON对象聚合为一个数组。结合GROUP BY对parent表的主键分组，确保每个parent对应一条包含所有子项的JSON结果：

SELECT JSON_OBJECT(
           parent.*, 
           'children' VALUE JSON_ARRAYAGG(JSON_OBJECT(child.*))
       ) AS parent_with_children
FROM parent
JOIN child ON child.parent_id = parent.id
WHERE parent.id = 1
GROUP BY parent.id, parent.last_name;

说明： #

• JSON_ARRAYAGG(JSON_OBJECT(child.*))会将当前parent关联的所有child行转换为JSON对象，并聚合到一个数组中；
• GROUP BY parent.id, parent.last_name确保按parent的唯一标识分组，避免生成多条结果；
• 因parent表主键是id，last_name是非主键字段，GROUP BY需包含所有非聚合的parent字段。

如果需要让JSON键名保持小写（原示例为大写，按需调整），可指定字段映射：

SELECT JSON_OBJECT(
           'id' VALUE parent.id, 
           'last_name' VALUE parent.last_name,
           'children' VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                   'id' VALUE child.id,
                   'parent_id' VALUE child.parent_id,
                   'name' VALUE child.name
               )
           )
       ) AS parent_with_children
FROM parent
JOIN child ON child.parent_id = parent.id
WHERE parent.id = 1
GROUP BY parent.id, parent.last_name;

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