从Oracle SQL创建嵌套的json数据

roejwanj  于 2023-08-04  发布在  Oracle
关注(0)|答案(2)|浏览(154)

我正在尝试使用Oracle SQL创建嵌套的JSON对象。我能够创建JSON对象,其中层次结构级别是预定义的。在这种情况下,它是动态的,并且无法为该嵌套数据找到SQL或PLSQL解决方案。
我有一个表,下面的数据,
Table data
我想使用Oracle SQL或PLSQL输出如下

{
  "data": [
    {
      "ID": 1,
      "NAME": "India",
      "child": [
        {
          "ID": 3,
          "NAME": "FINANCE",
          "child": [
            {
              "ID": 5,
              "NAME": "HR"
            }
          ]
        }
      ]
    },
    {
      "ID": 2,
      "NAME": "Canada",
      "child": [
        {
          "ID": 4,
          "NAME": "IT"
        }
      ]
    }
  ]
}

字符串
有人能帮我吗?

lmvvr0a8

lmvvr0a81#

你可以创建一个递归函数:

CREATE FUNCTION generate_json(
  i_parent_id IN NUMBER
) RETURN CLOB
IS
  v_json CLOB;
BEGIN
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'ID' VALUE id,
             KEY 'name' VALUE name,
             KEY 'child' VALUE generate_json(id) FORMAT JSON ABSENT ON NULL
           )
         )
  INTO   v_json
  FROM   table_name
  WHERE  parent_id = i_parent_id;

  RETURN v_json;
END;
/

字符串
其中,对于样本数据:

CREATE TABLE table_name(id, name, parent_id) AS
SELECT 1, 'INDIA',   0 FROM DUAL UNION ALL
SELECT 2, 'CANADA',  0 FROM DUAL UNION ALL
SELECT 3, 'FINANCE', 1 FROM DUAL UNION ALL
SELECT 4, 'IT',      2 FROM DUAL UNION ALL
SELECT 5, 'HR',      3 FROM DUAL;


然后:

SELECT JSON_OBJECT(KEY 'data' VALUE generate_json(0)) AS json FROM DUAL;


输出:
| JSON |
| ------------ |
| {"data":"[{"ID":1,"name":"INDIA","child":[{"ID":3,"name":"FINANCE","child":[{"ID":5,"name":"HR"}]}]},{"ID":2,"name":"CANADA","child":[{"ID":4,"name":"IT"}]}]"} |
fiddle

iezvtpos

iezvtpos2#

没有函数:你可以创建一个视图(最外面的JSON_QUERY只是为了漂亮的打印)
https://dbfiddle.uk/XgZ_IluN

WITH data (id, name, parent_id) as (
        select 1, 'INDIA', 0 from dual union all
        select 2, 'CANADA', 0 from dual union all
        select 3, 'FINANCE', 1 from dual union all
        select 4, 'IT', 2 from dual union all
        select 5, 'HR', 3 from dual 
    ),
    rel_hier( id, name, parent_id, lvl ) AS (
      SELECT id, name, parent_id, 1
      FROM data
      WHERE parent_id = 0
      UNION ALL
      SELECT n.id, n.name, n.parent_id,
        lvl + 1
      FROM rel_hier h
      JOIN data n on n.parent_id = h.id
    )
    SEARCH DEPTH FIRST BY id SET rn
    , rel_hier_with_leadlag AS (
      SELECT r.*
        , LAG(lvl) OVER(ORDER BY rn) AS lag_lvl
        , LEAD(lvl,1) OVER(ORDER BY rn) AS llead_lvl -- we need to know which the latest node
        , LEAD(lvl,1,1) OVER(ORDER BY rn) AS lead_lvl  -- for the latest node we need to use 1 instead of NULL
        , JSON_OBJECT(
               'id'   value id
             , 'name' value name
          ABSENT ON NULL
          RETURNING CLOB
        ) js
        FROM rel_hier r
    )
    SELECT
      JSON_QUERY(   
         XMLCAST(   
          XMLAGG(
            XMLELEMENT(e,
              CASE WHEN rn = 1 THEN '[' END ||
              CASE
                WHEN lvl - lag_lvl = 1 THEN ',"children":['    -- Level incremented by one, so child level, start array
                WHEN lvl > 1 then ','                          -- appending when not first level
                WHEN rn>1 AND parent_id = 0 THEN ','      -- appending when a root node but not the first one
              END ||
              SUBSTR(js, 1, LENGTH(js) - 1) ||                  -- remove last brace, as we are controlling children
              CASE
                WHEN lvl >= lead_lvl then '}' ||               -- Level same or greater than next level, so close json_object
                     RPAD(' ', (lvl - lead_lvl) * 2 + 1, ']}') -- and add as many closing array / object blocks as required
              END ||
              CASE WHEN llead_lvl IS NULL THEN ']' END          -- when the latest node 
            ) 
            ORDER BY rn
          )
        AS CLOB
      )
      , '$' RETURNING CLOB PRETTY
    ) as js

FROM rel_hier_with_leadlag r
;

字符串

相关问题