搭建环境
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