JSON Data Type
The
JSON
data type is an Oracle optimized binary JSON format called OSON. It is designed for faster query and DML performance in the database and in database clients from version 20c/21c upward.
We create a column in a table using the
JSON
data type, much like we would with any other data type.
-- drop table t1 purge;
create table t1 (
id number generated always as identity,
json_data json,
constraint ta_pk primary key (id)
We can populate the table using JSON data from a number of sources using the
JSON
constructor.
declare
l_varchar2 varchar2(32767);
l_clob clob;
l_blob blob;
begin
l_varchar2 := '{"fruit":"apple","quantity":10}';
l_clob := '{"fruit":"orange","quantity":20}';
l_blob := utl_raw.cast_to_raw('{"fruit":"banana","quantity":30}');
insert into t1 (json_data) values (json(l_varchar2));
insert into t1 (json_data) values (json(l_clob));
insert into t1 (json_data) values (json(l_blob));
commit;
Some inserts will work fine without explicitly using the JSON constructor, but it makes sense to use it explicitly.
The data is stored in the table in binary format, so a basic query isn't very useful.
set linesize 200
column json_data format a65
select * from t1;
ID JSON_DATA
---------- -----------------------------------------------------------------
1 7B226672756974223A226170706C65222C227175616E74697479223A31307D
2 7B226672756974223A226F72616E6765222C227175616E74697479223A32307D
3 7B226672756974223A2262616E616E61222C227175616E74697479223A33307D
Query JSON Data
The
JSON_SERIALIZE
function is used to convert JSON from any supported type into text. It was introduced in Oracle 19c, but it has been extended to support the
JSON
data type.
set linesize 200
column json_data format a50
select id, json_serialize(json_data) as json_data from t1;
ID JSON_DATA
---------- --------------------------------------------------
1 {"fruit":"apple","quantity":10}
2 {"fruit":"orange","quantity":20}
3 {"fruit":"banana","quantity":30}
We could query values using the normal SQL/JSON functions introduced in previous releases, which also support the new
JSON
type.
Here is an example of using the
JSON_VALUE
function.
column fruit format a10
select a.id,
json_value(a.json_data, '$.fruit') as fruit,
json_value(a.json_data, '$.quantity' returning number) as quantity
from t1 a
order by 1;
ID FRUIT QUANTITY
---------- ---------- ----------
1 apple 10
2 orange 20
3 banana 30
Here is an example of using the
JSON_QUERY
function.
column fruit format a10
column quantity format a10
select a.id,
json_query(a.json_data, '$.fruit' returning varchar2) as fruit,
json_query(a.json_data, '$.quantity' returning varchar2) as quantity
from t1 a
order by 1;
ID FRUIT QUANTITY
---------- ---------- ----------
1 "apple" 10
2 "orange" 20
3 "banana" 30
Here is an example of using the
JSON_TABLE
function.
select a.id,
jt.fruit,
jt.quantity
from t1 a,
json_table(a.json_data, '$'
columns (fruit varchar2(10 char) path '$.fruit',
quantity number path '$.quantity')) jt;
ID FRUIT QUANTITY
---------- ---------- ----------
1 apple 10
2 orange 20
3 banana 30
We can also query the data using dot notation. Remember the data is binary, so we have to convert it to text using the
JSON_SERIALIZE
function.
select a.id,
json_serialize(a.json_data.fruit) as fruit,
json_serialize(a.json_data.quantity) as quantity
from t1 a
order by 1;
ID FRUIT QUANTITY
---------- ---------- ----------
1 "apple" 10
2 "orange" 20
3 "banana" 30
There are a number of new member functions, as well as overloads of existing member functions that support the new data type.
