The JSON data type was introduced in the Oracle 20c preview release to provide native JSON support and improve the performance of JSON processing. It has become generally available in Oracle 21c.
◉ 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;
end;
/
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
SQL>
◉ 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}
SQL>
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
SQL>
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
SQL>
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
SQL>
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
SQL>
◉ JSON_SCALAR Function
The JSON_SCALAR function creates an instance of a JSON type from a SQL scalar value.
column scalar_number format A20
column scalar_string format A20
column scalar_date format A50
select json_scalar(1) as scalar_number,
json_scalar('string') as scalar_string,
json_scalar(date '2020-12-13') as scalar_date
from dual;
SCALAR_NUMBER SCALAR_STRING SCALAR_DATE
-------------------- -------------------- --------------------------------------------------
31 22737472696E6722 22323032302D31322D31335430303A30303A303022
SQL>
We can see what we created by converting the value back to text using the JSON_SERIALIZE function.
select json_serialize(json_scalar(1)) as scalar_number,
json_serialize(json_scalar('string')) as scalar_string,
json_serialize(json_scalar(date '2020-12-13')) as scalar_date
from dual;
SCALAR_NUMBER SCALAR_STRING SCALAR_DATE
-------------------- -------------------- --------------------------------------------------
1 "string" "2020-12-13T00:00:00"
SQL>
◉ PL/SQL Object Types for JSON Support
For many operations is may be simpler to use the JSON_TRANSFORM function, introduced in Oracle database 21c, rather than PL/SQL Object Types for JSON. If you do need to use PL/SQL Object Types for JSON, that's no problem.
The JSON_OBJECT_T constructor supports the new JSON data type. The following example retrieves a JSON value from the T1 table and converts it to a JSON_OBJECT_T type. We can then process it with the PL/SQL Object Types for JSON.
set serveroutput on
declare
l_json JSON;
l_obj json_object_t;
begin
-- Get the JSON data.
select json_data
into l_json
from t1
where id = 1;
-- Create a JSON_OBJECT_T object and output the contents.
l_obj := json_object_t(l_json);
dbms_output.put_line('l_obj.stringify = ' || l_obj.stringify);
end;
/
l_obj.stringify = {"fruit":"apple","quantity":10}
PL/SQL procedure successfully completed.
SQL>
set serveroutput on
declare
l_json JSON;
l_obj json_object_t;
begin
-- Get the JSON data.
select json_data
into l_json
from t1
where id = 1;
-- Create a JSON_OBJECT_T object and output the contents.
l_obj := json_object_t(l_json);
dbms_output.put_line('l_obj.stringify = ' || l_obj.stringify);
-- Convert it back to JSON.
l_json := l_obj.to_json;
dbms_output.put_line('l_json = ' || json_serialize(l_json));
-- Update the JSON column.
update t1
set json_data = l_json
where id = 1;
end;
/
l_obj.stringify = {"fruit":"apple","quantity":10}
l_json = {"fruit":"apple","quantity":10}
PL/SQL procedure successfully completed.
SQL>
There are a number of new member functions, as well as overloads of existing member functions that support the new data type.
Posts
0 comments:
Post a Comment