连接json_object多个结果行到一个oracle

Question

基本上我有一个select从表中获取数据并构建一个json:

SELECT JSON_OBJECT(KEY 'data' VALUE
JSON_ARRAYAGG(JSON_object(KEY 'code'     VALUE tt.code,
KEY 'name'     VALUE tt.name,
KEY 'location' VALUE
tt.location NULL ON NULL)
RETURNING CLOB),
KEY 'header' VALUE
JSON_OBJECT(KEY 'vr_code' VALUE 'team1',
KEY 'date'    VALUE
TO_DATE('2022-12-31', 'YYYY-MM-DD') RETURNING CLOB)
RETURNING CLOB) l_json_data
FROM thetable tt

目前我得到的是这个结构，其中每个组在单独的行中返回，我如何"连接";这样它们就会被填入一个数据中;数组对象?当前JSON结构:

{
"data": [
{
"code": "60",
"name": "michael",
"location": "canada"
},
{
"code": "60",
"name": "ken",
"location": "united states"
}
],
"header": {
"vr_code": "team1",
"date": "2022-12-31T00:00:00"
}
};
{
"data": [
{
"code": "70",
"name": "jim",
"location": "united states"
},
{
"code": "70",
"name": "leslie",
"location": "mexico"
}
],
"header": {
"vr_code": "team1",
"date": "2022-12-31T00:00:00"
}
}

所需的JSON结构:

{
"data": [
{
"code": "60",
"name": "michael",
"location": "canada"
},
{
"code": "60",
"name": "ken",
"location": "united states"
},
{
"code": "70",
"name": "jim",
"location": "united states"
},
{
"code": "70",
"name": "leslie",
"location": "mexico"
}
],
"header": {
"vr_code": "team1",
"date": "2022-12-31T00:00:00"
}
}

Answer 1

由于您的查询返回多行，因此您的当前查询将类似于:

SELECT JSON_OBJECT(
KEY 'data' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'code' VALUE code,
KEY 'name' VALUE name,
KEY 'location' VALUE location NULL ON NULL
)
RETURNING CLOB
) FORMAT JSON,
KEY 'header' VALUE JSON_OBJECT(
KEY 'vr_code' VALUE 'team1',
KEY 'date' VALUE DATE '2022-12-31'
RETURNING CLOB
)
RETURNING CLOB
) AS l_json_data
FROM   thetable
GROUP BY something

对于样本数据:

CREATE TABLE thetable(code, name, location, something) AS
SELECT '60', 'michael', 'canada', 1 FROM DUAL UNION ALL
SELECT '70', 'jim', 'united states', 2 FROM DUAL;

输出:

L_JSON_DATA tbody> <<tr>{"data"[{"code"60","name":"michael","location":"canada"}],"header":{"vr_code"team1","date":"2022 - 12 - 31 t00:00:00"}}{"data"[{"code"70","name":"jim","location":"曼联states"}],"header":{"vr_code"team1","date":"2022 - 12 - 31 t00:00:00"}}