OracleSQL：將PL/SQL中的JSON決議為表或陣列

我需要將 JSON 決議為表或陣列。因為這對我來說是全新的，所以我使用谷歌尋求幫助，但有些東西對我不起作用：/

我的 JSON 看起來像：

{
  "statusCode": 200,
  "isValid": true,
  "errors": [],
  "result": {
    "optimizationData": [
      {
        "name": "out",
        "content": "ID_LIMIT;TIME_STAMP_FROM;DIRECTION;ID_MODEL_CONSTRAINT\n1;202109222200;G;2_7_1_G\n1;202109232200;G;2_3_1_G\n2;202109222200;G;2_3_1_G\n3;202109222200;G;3_3_1_P\n"
      },
      {
        "name": "unit_out",
        "content": "ID_LIMIT;CODE_UNIT;TIME_STAMP_FROM;TIME_STAMP_TO;VARIABLE;VALUE\n1;BEL 2-02;202109222200;202109232200;RelaxationPlus;10\n1;BEL 2-05;202109222200;202109232200;RelaxationPlus;10\n2;WLO 1-01;202109222200;202109232200;RelaxationMinus;10\n"
      }
    ]
  }
}

從上面的 JSON 我想創建如下表（或陣列）（名稱的示例預期資料：“unit_out”）：

到目前為止，我已經嘗試撰寫一個函式來回傳“內容”值，但我得到了 ORA-30625（不允許在 null self 引數上調度方法）

CREATE OR REPLACE FUNCTION parse_json (json IN VARCHAR2)
  RETURN VARCHAR2 IS
  json_obj_in  JSON_OBJECT_T;
  json_arr     JSON_ARRAY_T;
  json_elem    JSON_ELEMENT_T;
  json_obj     JSON_OBJECT_T;
  name         varchar2(32000);
  content      varchar2(32000);

BEGIN
  json_obj_in := JSON_OBJECT_T.parse(json);

  json_arr := json_obj_in.get_Array('result');

  FOR i IN 0 .. json_arr.get_size - 1 --NVL(json_arr.FIRST, 1) .. NVL(json_arr.LAST, 0)
  LOOP
    json_obj := JSON_OBJECT_T(json_arr.get(i));

    name := json_obj.get_Object('optimizationData').get_string('name');
    content := json_obj.get_Object('optimizationData').get_string('content');

  END LOOP;

  RETURN json_obj_in.to_string;
END;

我不確定出了什么問題，或者我對這項任務的處理方法是否正確。

任何人都可以幫忙嗎？

謝謝

uj5u.com熱心網友回復：

從 Oracle 12 開始，您可以在 SQL 查詢中進行所有決議。您的主要問題不是 JSON，而是您將資料保存在字串中，您需要將它們拆分為行，然后拆分為值，并將值與標題相關聯：

SELECT p.*
FROM   (
  SELECT l.lineno,
         kv.key,
         kv.value
  FROM   table_name t
         CROSS APPLY JSON_TABLE(
           t.value,
           '$.result.optimizationData[*]?(@.name == "unit_out")'
           COLUMNS
             content CLOB PATH '$.content'
         ) j
         CROSS JOIN LATERAL (
           SELECT LEVEL AS lineno,
                  REGEXP_SUBSTR(j.content, '. ', 1, 1 ) AS header,
                  REGEXP_SUBSTR(j.content, '. ', 1, LEVEL ) AS line
           FROM   DUAL
           WHERE  LEVEL > 1
           CONNECT BY LEVEL <= REGEXP_COUNT(j.content, '. ')
         ) l
         CROSS JOIN LATERAL (
           SELECT CAST(REGEXP_SUBSTR(header, '[^;] ', 1, LEVEL) AS VARCHAR2(4000))
                    AS key,
                  CAST(REGEXP_SUBSTR(line, '[^;] ', 1, LEVEL) AS VARCHAR2(4000))
                    AS value
           FROM   DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT(header, '[^;] ')
         ) kv
) pt
PIVOT (
  MAX(value)
  FOR key IN (
    'ID_LIMIT'        AS id_limit,
    'CODE_UNIT'       AS code_limit,
    'TIME_STAMP_FROM' AS time_stamp_from,
    'TIME_STAMP_TO'   AS time_stamp_to,
    'VARIABLE'        AS variable,
    'VALUE'           AS value
  )
) p

其中，對于樣本資料：

CREATE TABLE table_name (value BLOB CHECK (value IS JSON));

INSERT INTO table_name (value) VALUES (
'{
  "statusCode": 200,
  "isValid": true,
  "errors": [],
  "result": {
    "optimizationData": [
      {
        "name": "out",
        "content": "ID_LIMIT;TIME_STAMP_FROM;DIRECTION;ID_MODEL_CONSTRAINT\n1;202109222200;G;2_7_1_G\n1;202109232200;G;2_3_1_G\n2;202109222200;G;2_3_1_G\n3;202109222200;G;3_3_1_P\n"
      },
      {
        "name": "unit_out",
        "content": "ID_LIMIT;CODE_UNIT;TIME_STAMP_FROM;TIME_STAMP_TO;VARIABLE;VALUE\n1;BEL 2-02;202109222200;202109232200;RelaxationPlus;10\n1;BEL 2-05;202109222200;202109232200;RelaxationPlus;10\n2;WLO 1-01;202109222200;202109232200;RelaxationMinus;10\n"
      }
    ]
  }
}'
);

輸出：

LINENO	ID_LIMIT	CODE_LIMIT	TIME_STAMP_FROM	TIME_STAMP_TO	多變的	價值
2	1	貝爾 2-02	202109222200	202109232200	放松加	10
3	1	貝爾 2-05	202109222200	202109232200	放松加	10
4	2	世界勞工組織 1-01	202109222200	202109232200	放松減	10

db<>在這里擺弄

uj5u.com熱心網友回復：

我給你留下一個對我有用的例子Oracle 12c：

INSERT INTO table(param_id, name, label, parameterIndex, notNull, parameterTypeName, default_value, lov_query)
      SELECT param_id, name, label, parameterIndex, notNull, parameterTypeName, default_value, lov_query FROM JSON_TABLE(l_json,
      '$.parameters[*]' COLUMNS (
        param_id PATH '$.id', name PATH '$.name', 
        label PATH '$.label', 
        parameterIndex PATH '$.parameterIndex', 
        notNull PATH '$.notNull', 
        parameterTypeName PATH '$.parameterType.parameterTypeName', 
        default_value PATH '$.parameterAttributes.attributeSelectedOption.attributeValue',
        lov_query PATH '$.parameterQuery'
        ) );

我不知道這是否被認為是一種好的做法，如果我錯了，有人會糾正我。這對我有用。

標籤：sql json 甲骨文 plsql oracle19c

上一篇：binary_double不應該在Oracle中存盤比數字更高的值嗎？

下一篇：如何在使用分組功能時在mysql中添加字串或字符