Oracle解析JSON

一、Oracle解析JSON格式欄位

在Oracle11g中，可以使用VARCHAR2作為JSON字元串的文本數據類型

CREATE TABLE json_table (
   id       NUMBER GENERATED ALWAYS AS IDENTITY,
   attr     VARCHAR2(4000) CONSTRAINT json_ck CHECK ( attr IS JSON )
);

在Oracle12c中，可以使用native JSON數據類型，VARBINARY JSON類型，CLOB JSON類型

CREATE TABLE json_tab (
   id       NUMBER GENERATED ALWAYS AS IDENTITY,
   jdoc     JSON,
   CONSTRAINT json_ck CHECK ( jdoc IS JSON )
);

二、Oracle解析JSON語句

Oracle可以使用JSON_TABLE函數來查詢JSON字元串中的數據

SELECT *
FROM   JSON_TABLE(
         '{"id": 100, "name": "John", "age": 25, "email": "john@example.com"}',
         '$'
         COLUMNS (
            id    NUMBER    PATH '$.id',
            name  VARCHAR2(50) PATH '$.name',
            age   NUMBER    PATH '$.age',
            email VARCHAR2(100) PATH '$.email'
         )
      );

結果：

  ID NAME          AGE EMAIL
---- ---------- ------ ------------------------
 100 John           25 john@example.com

三、Oracle解析JSON函數

（1）Oracle解析JSON格式函數

可以使用JSON_OBJECT、JSON_ARRAY、JSON_OBJECTAGG和JSON_ARRAYAGG函數來構建JSON數據

SELECT JSON_OBJECT('name' VALUE 'John', 'age' VALUE 25) 
FROM   DUAL;

結果：

{ "name" : "John", "age" : 25 }

（2）Oracle解析JSON字元串

可以使用JSON_QUERY函數來解析JSON字元串，可以返回一個標量或一個JSON對象、數組或標量值

SELECT JSON_QUERY('{"id": 100, "name": "John", "age": 25, "email": "john@example.com"}',
                  '$.id')
FROM   DUAL;

結果：

100

（3）Oracle解析JSON list

可以使用JSON_TABLE函數來解析JSON數組，JSON列是JSON數組中的元素的標量或對象

SELECT *
FROM   JSON_TABLE(
         '["apple", "orange", "banana"]',
         '$[*]'
         COLUMNS (
            id FOR ORDINALITY,
            fruit PATH '$'
         )
      );

結果：

 ID FRUIT 
--- ------
  1 apple 
  2 orange
  3 banana

（4）根據鍵取值

可以使用JSON_VALUE函數來根據鍵獲取值

SELECT JSON_VALUE('{"id": 100, "name": "John", "age": 25, "email": "john@example.com"}',
                   '$.name')
FROM   DUAL;

結果：

John

（5）Oracle解析JSON獲取欄位

可以使用JSON_EXISTS函數來確定JSON文檔中是否存在具有指定鍵或路徑的值

SELECT JSON_EXISTS('{"id": 100, "name": "John", "age": 25, "email": "john@example.com"}',
                     '$.name')
FROM   DUAL;

結果：

TRUE