Oracle解析JSON

发布时间：2023-05-19

一、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
推荐文章