Creating a table for JSON documents
create table tq84_json_documents (
id number generated always as identity,
doc clob,
--
constraint json_doc_pk primary key (id),
constraint doc_is_json check(doc is json)
);
insert into tq84_json_documents (doc) values ('{num: 1}');
-- check constraint violation
-- insert into tq84_json_documents (doc) values ('{num: }');
Querying JSON data
select
*
from
json_table('
{
"num" : 42,
"txt" : "Hello world",
"ary" : ["elem_0", 1]
}',
'$'
columns (
num number path '$.num' ,
txt varchar2(20) path '$.txt' ,
ary varchar2(20) FORMAT JSON path '$.ary' ,
ary_1st_elem varchar2(20) path '$.ary[0]',
ary_2nd_elem number path '$.ary[1]'
)
);
The following JSON document is very similar to the previous one - but it contains an array of objects. Using $[*] returns a record for each of these objects:
select
*
from
json_table('
[
{
"num" : 42,
"txt" : "Hello world",
"ary" : ["elem_0", 1]
},
{
"num" : 99,
"txt" : "ninety-nine",
"ary" : ["red balllons", 55.5]
},
{
"num" : 17,
"txt" : "xyz",
"ary" : ["abc", 1.7]
}
]',
'$[*]' -- Note the [*]
columns (
num number path '$.num' ,
txt varchar2(20) path '$.txt' ,
-- ary varchar2(20) format json path '$.ary' ,
ary_1st_elem varchar2(20) path '$.ary[0]',
ary_2nd_elem number path '$.ary[1]'
)
);
Misc / TODO
Because Oracle treats a string with no characters (
'') as
null, it's not possible to constract an empty JSON string (
"").
to_json()
Internally, Oracle processes JSON data in the
UTF-8 encoding.
JSON datatype
create table tq84_json( j json );
create table tq84_json( j blob, check (j is json format oson) );