Oracle 12C对JSON支持

最新推荐文章于 2023-07-17 15:27:41 发布

朝闻道-夕死可矣

最新推荐文章于 2023-07-17 15:27:41 发布

阅读量4.1k

点赞数

分类专栏： -- Ora12c&13c

本文链接：https://blog.csdn.net/jc_benben/article/details/73770272

版权

-- Ora12c&13c 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

搭建环境

SQL> create table json_t(
  2  id raw(16) not null,
  3  data clob,
  4  constraint json_t_pk primary key(id),
  5  constraint json_t_json_chk check (data is JSON)
  6  );

表已创建。

可以加上strict 来强制限制，如： check (data is JSON(strict))，以下例子都是使用strict的
插入数据：

  1  insert into json_t(id,data)
  2  values(sys_guid(),
  3     '{
  4            "FirstName"      : "John",
  5            "LastName"       : "Doe",
  6            "Job"            : "Clerk",
  7            "Address"        : {
  8                                "Street"   : "99 ChaoYang",
  9                                "City"     : "My City",
 10                                "Country"  : "CHINA",
 11                                "Postcode" : "100000"
 12                               },
 13            "ContactDetails" : {
 14                                "Email"    : "john.doe@example.com",
 15                                "Phone"    : "86 123 123456",
 16                                "Twitter"  : "@johndoe"
 17                               },
 18            "DateOfBirth"    : "01-JAN-1980",
 19            "Active"         : true
 20*          }')
SQL>
SQL> /

已创建 1 行。

SQL> select count(1) from json_t;

  COUNT(1)
----------
         1

SQL> edit
已写入 file afiedt.buf

  1  insert into json_t(id,data)
  2  values(sys_guid(),
  3     '{
  4            "FirstName"      : "Jayne",
  5            "LastName"       : "Doe",
  6            "Job"            : "Manager",
  7            "Address"        : {
  8                                "Street"   : "99 ChaoYang",
  9                                "City"     : "My City",
 10                                "Country"  : "CHINA",
 11                                "Postcode" : "100000"
 12                               },
 13            "ContactDetails" : {
 14                                "Email"    : "john.doe@example.com",
 15                                "Phone"    : "86 123 123456",
 16                                "Twitter"  : "@johndoe"
 17                               },
 18            "DateOfBirth"    : "01-JAN-1982",
 19            "Active"         : false
 20*          }')
SQL> /

已创建 1 行。

SQL> edit
已写入 file afiedt.buf

  1  insert into json_t(id,data)
  2  values(sys_guid(),
  3     '{
  4            "FirstName"      : "Jayne",
  5            "LastName"       : "Doe",
  6            "Job"            : "Manager",
  7            "Address"        : {
  8                                "Street"   : "99 ChaoYang",
  9                                "City"     : "My City",
 10                                "Country"  : "CHINA",
 11                                "Postcode" : "100000"
 12                               },
 13            "ContactDetails" : {
 14                                "Email"    : "john.doe@example.com",
 15                                "Phone"    : "86 123 123456",
 16                                "Twitter"  : "@johndoe"
 17                               },
 18            "DateOfBirth"    : "01-JAN-1982"
 19*          }')
SQL> /

已创建 1 行。

-- 查询
SQL> SELECT a.data.FirstName,
  2         a.data.LastName,
  3         a.data.Address.Postcode AS Postcode,
  4         a.data.ContactDetails.Email AS Email
  5  FROM   json_t a
  6  /

FIRSTNAME       LASTNAME        POSTCODE   EMAIL
--------------- --------------- ---------- -------------------------
John            Doe             100000     john.doe@example.com
Jayne           Doe             100000     john.doe@example.com
Jayne           Doe             100000     john.doe@example.com

注意：json内的key一定区分大小写，否则查询不到数据

也可以返回json格式

SQL> select a.data.Address from json_t a;

ADDRESS
--------------------------------------------------------------------------------

{"Street":"99 ChaoYang","City":"My City","Country":"CHINA","Postcode":"100000"}
{"Street":"99 ChaoYang","City":"My City","Country":"CHINA","Postcode":"100000"}
{"Street":"99 ChaoYang","City":"My City","Country":"CHINA","Postcode":"100000"}

不做限制json数据类型格式实例：

SQL> create table json_t_no_constraint(
  2  id raw(16) not null,
  3  data clob,
  4  constraint json_t_nocon_pk primary key (id));

表已创建。

SQL> insert into json_t_no_constraint values(sys_guid(),'{"FirstName":"Loge"}');


已创建 1 行。

SQL> insert into json_t_no_constraint values(sys_guid(),'Loge');

已创建 1 行。

SQL> commit;

提交完成。

SQL> set feedback on
SQL> select json_value(a.data,'$.FirstName') as first_name from json_t_no_constr
aint a where a.data is json;

FIRST_NAME
--------------------------------------------------------------------------------

Loge

已选择 1 行。

SQL> select json_value(a.data,'$.FirstName') as first_name from json_t_no_constr
aint a;

FIRST_NAME
--------------------------------------------------------------------------------

Loge


已选择 2 行。

JSON_EXISTS

用于判断Json格式中指定key存在但是为空的value

SQL> COLUMN FirstName FORMAT A15
SQL> COLUMN LastName FORMAT A10
SQL> COLUMN Email FORMAT A25
SQL> COLUMN Phone FORMAT A15
SQL> COLUMN Twitter FORMAT A10
SQL> SET LONGSIZE 1000
SP2-0158: 未知的 SET 选项 "LONGSIZE"
SQL> SET LINESIZE 1000
SQL> SELECT a.data.FirstName,
  2         a.data.LastName,
  3         a.data.ContactDetails.Email AS Email,
  4         a.data.ContactDetails.Phone AS Phone,
  5         a.data.ContactDetails.Twitter AS Twitter
  6  FROM   json_t a
  7  WHERE  a.data.ContactDetails.Phone IS NULL
  8  AND    a.data.ContactDetails.Twitter IS NULL;

FIRSTNAME       LASTNAME   EMAIL                     PHONE           TWITTER
--------------- ---------- ------------------------- --------------- ----------
Loge            Doe        john.doe@example.com

已选择 1 行。

SQL> edit
已写入 file afiedt.buf

  1  SELECT a.data.FirstName,
  2         a.data.LastName,
  3         a.data.ContactDetails.Email AS Email,
  4         a.data.ContactDetails.Phone AS Phone,
  5         a.data.ContactDetails.Twitter AS Twitter
  6  FROM   json_t a
  7  WHERE  JSON_EXISTS(a.data.ContactDetails,'$.Phone' FALSE ON ERROR)
  8* AND    a.data.ContactDetails.Phone IS NULL
SQL> /

FIRSTNAME       LASTNAME   EMAIL                     PHONE           TWITTER
--------------- ---------- ------------------------- --------------- ----------
Loge            Doe        john.doe@example.com

已选择 1 行。

SQL> edit
已写入 file afiedt.buf

  1  SELECT a.data.FirstName,
  2         a.data.LastName,
  3         a.data.ContactDetails.Email AS Email,
  4         a.data.ContactDetails.Phone AS Phone,
  5         a.data.ContactDetails.Twitter AS Twitter
  6  FROM   json_t a
  7* WHERE  NOT JSON_EXISTS(a.data.ContactDetails,'$.Twitter' FALSE ON ERROR)
SQL> /

FIRSTNAME       LASTNAME   EMAIL                     PHONE           TWITTER
--------------- ---------- ------------------------- --------------- ----------
Loge            Doe        john.doe@example.com

已选择 1 行。

JSON_VALUE
用于从一个json文档中查询指定元素的值

SQL> COLUMN Active FORMAT A10
SQL> SELECT a.data.FirstName,
  2         a.data.LastName,
  3         JSON_VALUE(a.data, '$.Active') AS Active,
  4         JSON_VALUE(a.data, '$.Active' RETURNING NUMBER) AS ActiveNum
  5  FROM   json_t a
  6  /

FIRSTNAME       LASTNAME   ACTIVE      ACTIVENUM
--------------- ---------- ---------- ----------
John            Doe        true                1
Jayne           Doe        false               0
Jayne           Doe
Loge            Doe        true                1

已选择 4 行。

JSON_QUERY
此函数返回一个或者多个值的JSON片段，如果使用with wrapper将使用方括号包含

SQL> COLUMN cd FORMAT A50
SQL> SELECT a.data.FirstName,
  2         a.data.LastName,
  3         JSON_QUERY(a.data, '$.ContactDetails' WITH WRAPPER) AS cd
  4  FROM   json_t a
  5  /

FIRSTNAME       LASTNAME   CD
--------------- ---------- --------------------------------------------------
John            Doe        [{"Email":"john.doe@example.com","Phone":"86 123 1
                           23456","Twitter":"@johndoe"}]

Jayne           Doe        [{"Email":"john.doe@example.com","Phone":"86 123 1
                           23456","Twitter":"@johndoe"}]

Jayne           Doe        [{"Email":"john.doe@example.com","Phone":"86 123 1
                           23456","Twitter":"@johndoe"}]

Loge            Doe        [{"Email":"john.doe@example.com","Phone":""}]

已选择 4 行。

JSON_TABLE
此函数常用与在json相关的视创建中

SQL> COLUMN first_name FORMAT A10
SQL> COLUMN last_name FORMAT A10
SQL> COLUMN addr_country FORMAT A10
SQL> COLUMN contact_details FORMAT A40
SQL> CREATE VIEW JSON_T_V AS
  2  SELECT jt.first_name,
  3         jt.last_name,
  4         jt.addr_country,
  5         jt.contact_details
  6  FROM   json_t,
  7         JSON_TABLE(data, '$'
  8           COLUMNS (first_name    VARCHAR2(50 CHAR) PATH '$.FirstName',
  9                    last_name     VARCHAR2(50 CHAR) PATH '$.LastName',
 10               addr_country  VARCHAR2(50 CHAR) PATH '$.Address.Country',
 11                    contact_details  VARCHAR2(4000 CHAR)
 12                      FORMAT JSON WITH WRAPPER PATH '$.ContactDetails')) jt;

视图已创建。

SQL> select * from json_t_v;

FIRST_NAME LAST_NAME  ADDR_COUNT CONTACT_DETAILS
---------- ---------- ---------- ----------------------------------------
John       Doe        CHINA      [{"Email":"john.doe@example.com","Phone"
                                 :"86 123 123456","Twitter":"@johndoe"}]

Jayne      Doe        CHINA      [{"Email":"john.doe@example.com","Phone"
                                 :"86 123 123456","Twitter":"@johndoe"}]

Jayne      Doe        CHINA      [{"Email":"john.doe@example.com","Phone"
                                 :"86 123 123456","Twitter":"@johndoe"}]

Loge       Doe        China      [{"Email":"john.doe@example.com","Phone"
                                 :""}]

查询JSON列信息

SQL> COLUMN table_name FORMAT A15
SQL> COLUMN column_name FORMAT A15
SQL>
SQL> SELECT table_name,
  2         column_name,
  3         format,
  4         data_type
  5  FROM   user_json_columns;

TABLE_NAME      COLUMN_NAME     FORMAT             DATA_TYPE
--------------- --------------- ------------------ --------------------------
JSON_T          DATA            TEXT               CLOB

朝闻道-夕死可矣

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle 12C对JSON支持

搭建环境SQL> create table json_t( 2 id raw(16) not null, 3 data clob, 4 constraint json_t_pk primary key(id), 5 constraint json_t_json_chk check (data is JSON) 6 );表已创建。可以加上strict 来强制限制
复制链接

扫一扫