JSON in Oracle 12c Demo－DanBrother的部落格

CREATE TABLE json_test_tab (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_test_tab_pk PRIMARY KEY (id),
CONSTRAINT json_test_tab_json_chk CHECK (data IS JSON)
);

INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Irene Palmer",
"gender": "female",
"company": "SPEEDBOLT",
"email": "irenepalmer@speedbolt.com",
"phone": "+1 (845) 436-2413",
"address": "983 Roosevelt Place, Leyner, Illinois, 5767",
"registered": "2017-02-14T09:51:25 -08:00",
"latitude": 52.001456,
"longitude": 161.045489,
"friends": [
{
"id": 0,
"name": "Chelsea Waller"
},
{
"id": 1,
"name": "Thomas Mccall"
},
{
"id": 2,
"name": "Booth Crawford"
}
],
"favoriteFruit": "apple"
}');

INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Emma Mcdonald",
"gender": "female",
"company": "BYTREX",
"email": "emmamcdonald@bytrex.com",
"phone": "+1 (951) 413-2195",
"address": "143 Arkansas Drive, Leola, Maryland, 5138",
"registered": "2017-02-25T04:04:50 -08:00",
"latitude": -28.083351,
"longitude": 30.897873,
"friends": [
{
"id": 0,
"name": "Powers Turner"
},
{
"id": 1,
"name": "Hinton Delgado"
},
{
"id": 2,
"name": "Barrera Hutchinson"
}
],
"favoriteFruit": "banana"
}');

INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Solis Rollins",
"gender": "male",
"company": "GROK",
"email": "solisrollins@grok.com",
"phone": "+1 (911) 439-3918",
"address": "910 Bragg Court, Trail, Virginia, 3065",
"registered": "2014-07-15T02:58:32 -08:00",
"latitude": 37.970592,
"longitude": -79.152205,
"friends": [
{
"id": 0,
"name": "Deborah Parrish"
},
{
"id": 1,
"name": "House Rich"
},
{
"id": 2,
"name": "Bennett Aguirre"
}
],
"favoriteFruit": "apple"
}');

INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Dorthy Hoffman",
"gender": "female",
"company": "ARTIQ",
"email": "dorthyhoffman@artiq.com",
"phone": "+1 (806) 592-2352",
"address": "257 Clarkson Avenue, Vowinckel, Virgin Islands, 1478",
"registered": "2016-07-18T10:20:27 -08:00",
"latitude": 34.322025,
"longitude": 13.665359,
"friends": [
{
"id": 0,
"name": "Kristine Herrera"
},
{
"id": 1,
"name": "Branch Phillips"
},
{
"id": 2,
"name": "Wall Myers"
}
],
"favoriteFruit": "strawberry"
}');

INSERT INTO json_test_tab (id, data)
VALUES(SYS_GUID(),
'{
"name": "Medina Mcbride",
"gender": "male",
"company": "AVENETRO",
"email": "medinamcbride@avenetro.com",
"phone": "+1 (875) 470-2147",
"address": "863 Ridgecrest Terrace, Rockbridge, Vermont, 366",
"registered": "2014-11-03T08:32:50 -08:00",
"latitude": 81.576249,
"longitude": -41.531397,
"friends": [
{
"id": 0,
"name": "Angeline Powers"
},
{
"id": 1,
"name": "Mcdowell Perry"
},
{
"id": 2,
"name": "Luz Moody"
}
],
"favoriteFruit": "strawberry"
}');

commit;

col name format a20
col gender format a10
col company format a20
col favoriteFruit format a30
select a.data.name as name,a.data.gender as gender,a.data.company as company, a.data.favoriteFruit as favoriteFruit
from json_test_tab a
order by 1;
>>

create or replace view vw_single_json_test_tab
as
select jt.name,
jt.gender,
jt.company,
jt.friends
from json_test_tab,
JSON_TABLE(data, '$'
COLUMNS (name varchar2(30) PATH '$.name',
gender varchar2(10) PATH '$.gender',
company varchar2(30) PATH '$.company',
friends varchar2(300) FORMAT JSON WITH WRAPPER PATH '$.friends'
)
)jt
order by jt.name;

select * from vw_single_json_test_tab;
>>


create or replace view vw_nested_json_test_tab
as
select jt.name,
jt.gender,
   jt.company,
   jt.friend_no,
   jt.friend_name
from json_test_tab,
JSON_TABLE(data, '$'
COLUMNS (name varchar2(30) PATH '$.name',
gender varchar2(10) PATH '$.gender',
company varchar2(30) PATH '$.company',
NESTED PATH '$.friends[*]'
COLUMNS(friend_no number PATH '$.id',
friend_name varchar2(30) PATH '$.name'
)
)
)jt
order by jt.name,jt.friend_no;

set linesize 120
set pagesize 100
col name format a20
col gender format a10
col company format a20
col friend_name format a30
break on name on gender on company
select * from vw_nested_json_test_tab;
>>