在Oracle中将父子行转换为JSON

Oracle Database 12.2确实有许多JSON生成函数。但它们的用途有限。您需要递归地构建文档。

我相信这需要一点手工制作。

首先使用递归查询创建组织结构图,添加层次结构中每个人的级别。

然后通过以下方式构建json:

• 如果下一行的级别大于当前级别,则员工是经理。你需要启动一个子数组。否则返回当前行的json对象
• 如果当前行是树中的最后一行,则需要关闭n个数组和对象。n是这排树的深度减去1。
• 否则,如果下一行低于当前行,则需要关闭(当前层-下一层)数组和对象
• 如果下一个级别等于或小于当前级别,则添加逗号

它提供了如下信息:

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