sql - 如何从表中选择带有 oracle sql 中的 group by 子句的嵌套 json 对象?
问题描述
假设我有以下声明:
WITH t AS
(
SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A11' AS level_2_1, 'B11' AS level_2_2 FROM dual
UNION ALL
SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A12' AS level_2_1, 'B12' AS level_2_2 FROM dual
UNION ALL
SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A13' AS level_2_1, 'B13' AS level_2_2 FROM dual
UNION ALL
SELECT 'A' AS level_0, 'A1' AS level_1_1, 'object_1' AS level_1_2, 'A14' AS level_2_1, 'B14' AS level_2_2 FROM dual
UNION ALL
SELECT 'B' AS level_0, 'A1' AS level_1_1, 'object_2' AS level_1_2, 'A11' AS level_2_1, 'B15' AS level_2_2 FROM dual
UNION ALL
SELECT 'B' AS level_0, 'A1' AS level_1_1, 'object_2' AS level_1_2, 'A12' AS level_2_1, 'B16' AS level_2_2 FROM dual
UNION ALL
SELECT 'B' AS level_0, 'A1' AS level_1_1, 'object_2' AS level_1_2, 'A13' AS level_2_1, 'B17' AS level_2_2 FROM dual
)
SELECT * FROM t
我想要的输出如下所示:
+---------+---------------------------------------------------------------------------------+
| Level 0 | JSON |
+---------+---------------------------------------------------------------------------------+
| A | {"level_1_1":"A1","object_1":{"A11":"B11","A12":"B12","A13":"B13","A14":"B14"}} |
| B | {"level_1_1":"A1","object_2":{"A11":"B15","A12":"B16","A13":"B17"}} |
+---------+---------------------------------------------------------------------------------+
如何使用 select 语句获得此输出?
非常感谢您的帮助!
解决方案
您可以使用GROUP BY
,LISTAGG
和concatenation
如下:
SQL> with t as(
2 select 'A' as level_0, 'A1' as level_1_1, 'object_1' as level_1_2, 'A11' as level_2_1, 'B11' as level_2_2 from dual union all
3 select 'A' as level_0, 'A1' as level_1_1, 'object_1' as level_1_2, 'A12' as level_2_1, 'B12' as level_2_2 from dual union all
4 select 'A' as level_0, 'A1' as level_1_1, 'object_1' as level_1_2, 'A13' as level_2_1, 'B13' as level_2_2 from dual union all
5 select 'A' as level_0, 'A1' as level_1_1, 'object_1' as level_1_2, 'A14' as level_2_1, 'B14' as level_2_2 from dual union all
6 select 'B' as level_0, 'A1' as level_1_1, 'object_2' as level_1_2, 'A11' as level_2_1, 'B15' as level_2_2 from dual union all
7 select 'B' as level_0, 'A1' as level_1_1, 'object_2' as level_1_2, 'A12' as level_2_1, 'B16' as level_2_2 from dual union all
8 select 'B' as level_0, 'A1' as level_1_1, 'object_2' as level_1_2, 'A13' as level_2_1, 'B17' as level_2_2 from dual
9 )
10 select LEVEL_0,
11 '{"level_1_1":"'|| level_1_1 || '","'||level_1_2 ||'":{'
12 || LISTAGG('"' || level_2_1 || '":"' || level_2_2 || '"', ',')
13 WITHIN GROUP (ORDER BY level_2_1, level_2_2)
14 || '}}' AS JSON
15 from t
16 GROUP BY LEVEL_0, level_1_1, level_1_2;
LEVEL_0 JSON
---------- -------------------------------------------------------------------------------
A {"level_1_1":"A1","object_1":{"A11":"B11","A12":"B12","A13":"B13","A14":"B14"}}
B {"level_1_1":"A1","object_2":{"A11":"B15","A12":"B16","A13":"B17"}}
SQL>
推荐阅读
- adal - 护照 oauth 身份验证 - 令牌存储在 cookie 标头中
- git - 链接来自不同文件夹的文件,以便它们相互同步
- wpf - 当 StackPanel 超过一定宽度时动态调整按钮的位置
- python - 为什么打印的两行在脚本中没有对齐?
- php - 计算结果数组中的字符串数
- python - 根据条件在列表中形成组
- php - Laravel 7 - 更新唯一约束的问题
- regex - 需要一个 java.util.regex 来检索引号中的值
- clojure - 从一个元素开始,将函数 f 重复应用于每个新元素,同时将它们添加到向量中
- django - Elastic Beanstalk Python 3.7 上的 Django 中的容器命令失败