如何使用Oracle JSON_OBJECT和JSON_ARRAYAGG从多个列表聚合不同的值
如何使用Oracle JSON_OBJECT和JSON_ARRAYAGG从多个列表聚合不同的值
提问于 2021-08-01 13:31:04
需要创建一个Json_Object，其中可以包含多个嵌套的Json_objects、Json_arrays和Json_arrayaggs。
我用一些虚拟数据创建了这个表来演示这个问题：
create table test_tbl(
test_col1 varchar2(20),
test_col2 varchar2(20),
test_col3 varchar2(20),
test_col4 varchar2(20),
test_col5 varchar2(20),
test_col6 varchar2(20)
);
insert into test_tbl values('val0', 'val1', 'val2', 'val7', 'val11', 'val12');
insert into test_tbl values('val0', 'val3', 'val4', 'val7','val11', 'val12');
insert into test_tbl values('val0', 'val5', 'val6', 'val7','val13', 'val14');
insert into test_tbl values('val0', 'val5', 'val6', 'val7','val11', 'val12');
insert into test_tbl values('val0', 'val5', 'val6', 'val8','val11','val12');
insert into test_tbl values('val1', 'val9', 'val10', 'val7','val11', 'val12');
insert into test_tbl values('val1', 'val9', 'val10', 'val7','val13', 'val14');当使用以下查询创建Json_object时：
SELECT JSON_OBJECT (
'output' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'common' VALUE test_col1,
'list' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'key1' VALUE test_col2,
'key2' VALUE test_col3
)
),
'anotherlist' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'key1' VALUE test_col5,
'key2' VALUE test_col6
)
)
)
)
)
FROM (
SELECT DISTINCT
test_col1, test_col2, test_col3, test_col5, test_col6
FROM test_tbl
WHERE test_col4 = 'val7'
)
GROUP BY
test_col1这导致在聚合数组中使用重复键和值对跟随json -
{
"output": [
{
"common": "val0",
"list": [
{
"key1": "val5",
"key2": "val6"
},
{
"key1": "val3",
"key2": "val4"
},
{
"key1": "val1",
"key2": "val2"
},
{
"key1": "val5",
"key2": "val6"
}
],
"anotherlist": [
{
"key1": "val13",
"key2": "val14"
},
{
"key1": "val11",
"key2": "val12"
},
{
"key1": "val11",
"key2": "val12"
},
{
"key1": "val11",
"key2": "val12"
}
]
},
{
"common": "val1",
"list": [
{
"key1": "val9",
"key2": "val10"
},
{
"key1": "val9",
"key2": "val10"
}
],
"anotherlist": [
{
"key1": "val11",
"key2": "val12"
},
{
"key1": "val13",
"key2": "val14"
}
]
}
]
}而我所期望的Json是：
{
"output": [
{
"common": "val0",
"list": [
{
"key1": "val5",
"key2": "val6"
},
{
"key1": "val3",
"key2": "val4"
},
{
"key1": "val1",
"key2": "val2"
}
],
"anotherlist": [
{
"key1": "val13",
"key2": "val14"
},
{
"key1": "val11",
"key2": "val12"
}
]
},
{
"common": "val1",
"list": [
{
"key1": "val9",
"key2": "val10"
}
],
"anotherlist": [
{
"key1": "val11",
"key2": "val12"
},
{
"key1": "val13",
"key2": "val14"
}
]
}
]
}感谢您对如何获得上述预期Json的任何建议。
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-08-01 18:46:08
对第一对列使用一个DISTINCT子查询，然后对公共test_col1上的另一对列和JOIN使用第二个DISTINCT子查询。
SELECT JSON_OBJECT (
'output' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'common' VALUE c23.test_col1,
'list' VALUE c23.list,
'anotherlist' VALUE c56.anotherlist
)
)
)
FROM (
SELECT test_col1,
JSON_ARRAYAGG(
JSON_OBJECT(
'key1' VALUE test_col2,
'key2' VALUE test_col3
)
) AS list
FROM ( SELECT DISTINCT
test_col1, test_col2, test_col3
FROM test_tbl
WHERE test_col4 = 'val7'
)
GROUP BY test_col1
) c23
INNER JOIN (
SELECT test_col1,
JSON_ARRAYAGG(
JSON_OBJECT(
'key1' VALUE test_col5,
'key2' VALUE test_col6
)
) AS anotherlist
FROM ( SELECT DISTINCT
test_col1, test_col5, test_col6
FROM test_tbl
WHERE test_col4 = 'val7'
)
GROUP BY test_col1
) c56
ON ( c23.test_col1 = c56.test_col1 )产出：
{“输出”：[{“公共”："val0"，“列表”：{"key1“："val1"，"key2”}，{"key1“："val5"，"key2”："val6"}，{"key1“："val3"，"key2”："val4"}，“另一个列表”：{"key1“："val11"，"key2”："val12"}，{"key1“："val13"，"key2”："val14" }，{“公共”："val1"，"list“：{"key1”："val9"，"key2“："val10"}，“另一个列表”：{"key1“："val11"，"key2”："val12"}，{"key1“："val13"，"key2”："val14"}
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68610906
复制相关文章
相似问题
腾讯云开发者
