我在Oracle版本21 XE中有一个简单的1：n关系。例如：

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');

字符串
我几乎可以用下面的代码将其转换为JSON：

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

型
这产生：

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

型
但是，我希望所有3个通过外键相关的孩子都作为JSON数组的一部分（而不是3个单独的结果）。换句话说，类似于：

{"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"}]}

型
我该怎么办？