转 Oracle 12C对JSON支持
###sample1
转自 https://blog.csdn.net/jc_benben/article/details/73770272
搭建环境
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相关的视创建中
9 JSON_TABLE
SQL/JSON函数JSON_TABLE创建JSON数据的关系视图。
它将JSON数据计算的结果映射到关系行和列中。
可以使用SQL将函数返回的结果作为虚拟关系表进行查询。J
SON_TABLE的主要目的是为JSON数组中的每个对象创建一行关系数据，并将该对象中的JSON值作为单独的SQL列值输出。
path 来自
$ 该json 列
columns 将该json列写成column
json_table 作为单独的一个虚拟表
department VARCHAR2(4000) 虚拟表的字段表达式
SELECT p.product_id,
p.product_name,
g.department,
p.product_details
FROM products p , json_table (p.product_details, '$' COLUMNS ( department VARCHAR2(4000) path '$.gender') ) g
sql:
json_table (m.product_details, '$' columns ( brand varchar2(4000) path '$.brand') ) b
解答:
从 m 表的 .product_details 的字段的brand 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"
:""}]
https://livesql.oracle.com/apex/livesql/file/content_KI449O6F0FYCXV53X6DDK99ZB.html
-
Statement 1
create table camping_orders (orders_json BLOB, check (orders_json is JSON)) -
Statement 2
insert into camping_orders values (' { "orderNumber": 3737719, "customerName": "Arnold S", "date": "2020-08-17T10:15:01", "orderItems": [{ "itemNo": 83738, "name": "M79 grenade launcher", "quantity": "1", "unitPrice": 299.99 }, { "itemNo": 92748, "name": "40×46mm grenade", "quantity": "25", "unitPrice": 19.50 }, { "itemNo": 84784, "name": "first aid kit with Hello Kitty bandaids", "quantity": "1", "unitPrice": 9.99 } ] }'); -
Statement 3
select jt.* from camping_orders, JSON_TABLE (orders_json, '$.orderItems[*]' COLUMNS ( ITEM_NO number path '$.itemNo', NAME varchar2(40) path '$.name', QUANTITY number path '$.quantity', UNIT_PRICE number(5,2) path '$.unitPrice' )) jt -
Statement 4the 'NESTED PATH' generates rows for the items in the array.
select jt.* from camping_orders, JSON_TABLE (orders_json, '$' COLUMNS ( ORDER_NUMBER number path '$.orderNumber', CUST_NAME varchar2(40) path '$.customerName', ORDER_DATE date path '$.date', NESTED PATH '$.orderItems[*]' COLUMNS( ITEM_NO number path '$.itemNo', NAME varchar2(40) path '$.name', QUANTITY number path '$.quantity', UNIT_PRICE number path '$.unitPrice' )))jt; -
Statement 5Above two examples use the full syntax of JSON_TABLE. This is a shorter version where the column name is derived from JSON key name. Since DATE is not a valid column name we need to explicitly rename that column. Also, if no data type is specified the default of VARCHAR2(4000) is used.
select * from camping_orders NESTED orders_json COLUMNS (orderNumber, customerName, "ORDER_DATE" path '$.date', NESTED orderItems[*] COLUMNS(itemNo, name, quantity, unitPrice));
查询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
————————————————
版权声明：本文为CSDN博主「朝闻道-夕死可矣」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/jc_benben/article/details/73770272
