決議PLSQL中沒有鍵的JSON串列

我想要做的是用來自 JSON 的資料填充一個表。該檔案的格式是這樣的。

[
  {
    "name": "Victor",
    "age": "20"
  },
  {
    "name": "Ana",
    "age": "23"
  }
]

我無法更改它的格式。

我嘗試使用APEX_JSON來決議它并逐行添加，但我什至不能使用GET_COUNT，我嘗試過的路徑都沒有。

資料庫是Oracle 11g，所以沒有 JSON_TABLE

uj5u.com熱心網友回復：

如果你能找到合適的 JSON 決議器，那么你應該使用它；但是，如果一個不可用，您可以自己決議它。在 Oracle 11gR2 中，您可以使用：

INSERT INTO table_name (name, age)
WITH jsondata (json) AS (
  SELECT '[
    {"name":"Victor", "age":"20"},
    {"name":"Ana", "age":"23"},
    {
      "name":"Betty",
      "age":"24"
    },
    {
      "age":"25",
      "name":"Carol"
    }
]' FROM DUAL
),
data (json, items, i, name, age) AS (
  SELECT json,
         REGEXP_COUNT(
           json,
               '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
           || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
           1,
           'n'
         ),
         1,
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             1,
             'n'
           ),
           '"name"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         ),
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             1,
             'n'
           ),
           '"age"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         )
  FROM   jsondata
UNION ALL
  SELECT json,
         items,
         i   1,
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             i   1,
             'n'
           ),
           '"name"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         ),
         REGEXP_SUBSTR(
           REGEXP_SUBSTR(
             json,
                 '\{\s*"name"\s*:\s*"(.*?)"\s*,\s*"age"\s*:\s*"(.*?)"\s*\}'
             || '|\{\s*"age"\s*:\s*"(.*?)"\s*,\s*"name"\s*:\s*"(.*?)"\s*\}',
             1,
             i   1,
             'n'
           ),
           '"age"\s*:\s*"(.*?)"',
           1,
           1,
           'n',
           1
         )
  FROM   data
  WHERE  i < items
)
SELECT name, age
FROM   data;

（注意：正則運算式不處理字串中的轉義引號，因為我假設它們不會出現在名稱中；但是，如果它們這樣做，那么.*?您可以使用(\(["\/bfnrt]|u[0-9a-fA-F]{4})|[^"])*.）

其中，給定表格：

CREATE TABLE table_name (name VARCHAR2(30), age NUMBER);

然后在插入之后：

SELECT * FROM table_name;

輸出：

名稱	年齡
勝利者	20
安娜	23
貝蒂	24
頌歌	25

db<>在這里擺弄

uj5u.com熱心網友回復：

上次使用 clob 變數完成此操作。嘗試這樣做：

DECLARE 
json_body clob := '[
    {"name":"Victor", "age":"20"},
    {"name":"Ana", "age":"23"}
    ]';
BEGIN
  FOR items IN (SELECT   *
    FROM   
     JSON_TABLE(json_body FORMAT JSON,'$[*]'
              COLUMNS (
                        name_ varchar (200) PATH '$.name',
                        age_ varchar (200) PATH '$.age')))
LOOP
    INSERT INTO T_DATA (
       name,
       age
       ) VALUES (
    items.name_, 
items.age_
);
END LOOP;
END;
/

這會將您的資料放入表格中，然后您可以使用它們

select * from T_DATA;

結果為： 結果

uj5u.com熱心網友回復：

你APEX_JSON可以這樣做：

DECLARE
  l_json_text VARCHAR2(32767);
  l_json_values    apex_json.t_values;
BEGIN  
  l_json_text := '[
    {"name":"Victor", "age":"20"},
    {"name":"Ana", "age":"23"}
]
';  
  apex_json.parse(
    p_values => l_json_values,
    p_source => l_json_text
  );
  DBMS_OUTPUT.put_line('----------------------------------------'); 
  FOR r IN 1 .. nvl(apex_json.get_count(p_path => '.', p_values => l_json_values),0) loop
    dbms_output.put_line(apex_json.get_varchar2(p_path => '[%d].name', p0 => r, p_values => l_json_values));
    dbms_output.put_line(apex_json.get_varchar2(p_path => '[%d].age', p0 => r, p_values => l_json_values));
    /* insert into your_table 
       (name,
       age
       ) 
       VALUES 
       (
            apex_json.get_varchar2(p_path => '[%d].name', p0 => r, p_values => l_json_values),
            apex_json.get_varchar2(p_path => '[%d].age', p0 => r, p_values => l_json_values)
        );
    */
  END loop;
  DBMS_OUTPUT.put_line('----------------------------------------'); 
END;
/

uj5u.com熱心網友回復：

您可以XMLTABLE與APEX_JSON.TO_XMLTYPE()功能一起使用以模擬JSON_TABLE諸如

WITH t(jsCol) AS
(
 SELECT '[
          {
            "name": "Victor",
            "age": "20"
          },
          {
            "name": "Anna",
            "age": "23"
          }
         ]' 
   FROM dual 
)
SELECT name, age
  FROM t,
       XMLTABLE('/json/row'
                PASSING APEX_JSON.TO_XMLTYPE(jsCol)
                COLUMNS 
                  name VARCHAR2(100) PATH 'name',
                  age  VARCHAR2(100) PATH 'age'
               )

標籤：

上一篇：如何在OracleSQL查詢中獲取COUNT=0的行？

下一篇：從左到右獲取數字