sql - 在oracle中将父子行转换为JSON
问题描述
有没有办法在 Oracle 中为父子关系数据创建 JSON 对象?例如组织结构。表包含
EmpId Name Title ManagerId
1 John GM 0
2 Smith Manager 1
3 Jason Manager 1
4 Will IP1 3
5 Jade AM 3
6 Mark IP2 5
7 Jane AM2 5
8 Tamara M1 1
9 Dory M2 1
预计会出现以下 JSON 对象。
{
'name': 'John',
'title': 'GM',
'children': [
{ 'name': 'Smith', 'title': 'manager' },
{ 'name': 'Jason', 'title': 'manager',
'children': [
{ 'name': 'Will', 'title': 'IP1' },
{ 'name': 'Jade', 'title': 'AM',
'children': [
{ 'name': 'Mark', 'title': 'IP2' },
{ 'name': 'Jane', 'title': 'AM2' }
]
}
]
},
{ 'name': 'Tamara', 'title': 'M1' },
{ 'name': 'Dory', 'title': 'M2' }
]
}
解决方案
Oracle Database 12.2 确实有许多 JSON 生成函数。但这些用途有限。您需要递归地构建文档。
我认为这需要一些手工制作。
首先使用递归查询来创建组织结构图,添加每个人在层次结构中的级别。
然后通过以下方式构建 JSON:
- 如果下一行的级别大于当前级别,则该员工是经理。你需要启动一个子数组。否则返回当前行的 JSON 对象
- 如果当前行是树中的最后一行,则需要关闭 N 个数组和对象。N 是该行在树中的深度减一。
- 否则如果下一行比当前级别低,则需要关闭(当前级别-下一级)数组和对象
- 然后如果下一个级别等于或小于当前级别,则添加逗号
这给出了类似的东西:
create table t (
EmpId int,
Name varchar2(10),
Title varchar2(10),
ManagerId int
);
insert into t values (1, 'John', 'GM' , 0 );
insert into t values (2, 'Smith', 'Manager' , 1 );
insert into t values (3, 'Jason', 'Manager' , 1 );
insert into t values (4, 'Will', 'IP1' , 3 );
insert into t values (5, 'Jade', 'AM' , 3 );
insert into t values (6, 'Mark', 'IP2' , 5 );
insert into t values (7, 'Jane', 'AM2' , 5 );
insert into t values (8, 'Tamar', 'M1' , 1 );
insert into t values (9, 'Dory', 'M2' , 1 );
commit;
with chart (
empid, managerid, name, title, lvl
) as (
select empid, managerid,
name, title, 1 lvl
from t
where empid = 1
union all
select t.empid, t.managerid,
t.name, t.title,
lvl + 1 lvl
from chart c
join t
on c.empid = t.managerid
) search depth first by empid set seq,
jdata as (
select case
/* The employee has reports */
when lead ( lvl ) over ( order by seq ) > lvl then
'{"name": "' || name ||
'", "title": "' || title ||
'", "children": ['
else
json_object ( 'name' value name, 'title' value title )
end ||
case
/* Close arrays & objects */
when lead ( lvl ) over ( order by seq ) is null then
lpad ( ']}', ( lvl - 1 ) * 2, ']}' )
when lead ( lvl ) over ( order by seq ) < lvl then
lpad ( ']}', ( lvl - lead ( lvl ) over ( order by seq ) ) * 2, ']}' )
end ||
case
/* Add closing commas */
when lead ( lvl ) over ( order by seq ) <= lvl then
','
end j,
lead ( lvl ) over ( order by seq ) nlvl,
seq, lvl
from chart
)
select json_query (
listagg ( j )
within group ( order by seq ),
'$' returning varchar2 pretty
) chart_json
from jdata;
CHART_JSON
{
"name" : "John",
"title" : "GM",
"children" :
[
{
"name" : "Smith",
"title" : "Manager"
},
{
"name" : "Jason",
"title" : "Manager",
"children" :
[
{
"name" : "Will",
"title" : "IP1"
},
{
"name" : "Jade",
"title" : "AM",
"children" :
[
{
"name" : "Mark",
"title" : "IP2"
},
{
"name" : "Jane",
"title" : "AM2"
}
]
}
]
},
{
"name" : "Tamar",
"title" : "M1"
},
{
"name" : "Dory",
"title" : "M2"
}
]
}
推荐阅读
- python - 使用python比较两个files.txt中的特定行
- flutter - 如何在颤动中激活和停用收藏夹图标
- css - 为什么我在谷歌浏览器的检查 (Ctrl+Shift+I) 中看到水平滚动条?
- javascript - '/' home nav item color is showing active even changing of NavLink in ReactJs
- python - 如何解决窗口 8 中 Visual Studio 代码中的导入错误?
- c++ - XCODE exec 文件仅在停止程序后运行
- ios - 如何使用情节提要在 Tableview Cell 中为 2 个标签和一个 UIImageView 设置约束?
- c++ - 为什么 multiset 使用它存储的对象的 operator<?
- html - 锚标签停止跨页面工作?
- php - 将json文件设置为环境变量