8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON_VALUE Function Enhancements in Oracle Database 23c
In Oracle database 23c the RETURNING
clause of the JSON_VALUE
function has been enhanced allowing it to convert JSON data to user-defined types.
- Setup
- Using JSON_VALUE to Instantiate a User-Defined Object Type
- Using JSON_VALUE to Instantiate a Collection
- Using JSON_VALUE to Instantiate a Boolean Type
- JSON_VALUE with Predicates
Related articles.
- Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
The examples in this article require the following objects. We create a table and populate it with some JSON data.
drop table if exists t1 purge; create table t1 ( id number, data json ); insert into t1 (id, data) values (1, '{"ID":1,"VAL1":"banana1","VAL2":"apple1"}'); insert into t1 (id, data) values (2, '{"ID":2,"VAL1":"banana2","VAL2":"apple2"}'); insert into t1 (id, data) values (3, '{"ID":3,"VAL1":"banana3","VAL2":"apple3"}'); commit;
We create an object type which matches the JSON data.
create or replace type t_obj as object ( id number, val1 varchar2(10), val2 varchar2(10) ); /
Using JSON_VALUE to Instantiate a User-Defined Object Type
In Oracle 23c the JSON_VALUE
function includes a RETURNING
clause, which allows us to convert JSON data to a user-defined type.
In the following example we use the JSON_VALUE
function to return the JSON data from the T1 table. We want the full contents of the JSON, so we use the '$' path, and reference our T_OBJ
object type in the RETURNING
clause.
select json_value(data, '$' returning t_obj) as data from t1 where id = 1; DATA(ID, VAL1, VAL2) -------------------------------------------------------------------------------- T_OBJ(1, 'banana1', 'apple1') SQL>
We can see the T_OBJ
object type has been instantiated based on the JSON in the specified row.
Using JSON_VALUE to Instantiate a Collection
In the previous example we limited the query to a single row. We could have queried all the rows.
select json_value(data, '$' returning t_obj) as data from t1; DATA(ID, VAL1, VAL2) -------------------------------------------------------------------------------- T_OBJ(1, 'banana1', 'apple1') T_OBJ(2, 'banana2', 'apple2') T_OBJ(3, 'banana3', 'apple3') SQL>
This means we can populate a collection of this object type.
In the following example we create a nested table type based on the T_OBJ
type, and a variable based on that type. We use a BULK COLLECT to populate the collection based on the previous query. We loop through the collection, displaying the values.
set serveroutput on declare type t_tab is table of t_obj; l_tab t_tab; begin select json_value(data, '$' returning t_obj) bulk collect into l_tab from t1; for i in 1 .. l_tab.count loop dbms_output.put_line(l_tab(i).id || ' : ' || l_tab(i).val1 || ' : ' || l_tab(i).val2); end loop; end; / 1 : banana1 : apple1 2 : banana2 : apple2 3 : banana3 : apple3 PL/SQL procedure successfully completed. SQL>
We repeat the previous example, but this time use a varray, rather than a nested table.
set serveroutput on declare type t_tab is varray(5) of t_obj; l_tab t_tab; begin select json_value(data, '$' returning t_obj) bulk collect into l_tab from t1; for i in 1 .. l_tab.count loop dbms_output.put_line(l_tab(i).id || ' : ' || l_tab(i).val1 || ' : ' || l_tab(i).val2); end loop; end; / 1 : banana1 : apple1 2 : banana2 : apple2 3 : banana3 : apple3 PL/SQL procedure successfully completed. SQL>
We repeat the previous example, but this time use an associative array (index by table).
set serveroutput on declare type t_tab is table of t_obj index by pls_integer; l_tab t_tab; begin select json_value(data, '$' returning t_obj) bulk collect into l_tab from t1; for i in 1 .. l_tab.count loop dbms_output.put_line(l_tab(i).id || ' : ' || l_tab(i).val1 || ' : ' || l_tab(i).val2); end loop; end; / 1 : banana1 : apple1 2 : banana2 : apple2 3 : banana3 : apple3 PL/SQL procedure successfully completed. SQL>
Using JSON_VALUE to Instantiate a Boolean Type
The RETURNING
clause can also be used to converts JSON data to built-in types. In the following example we use the JSON_VALUE
function to convert a JSON Boolean into a PL/SQL Boolean type. Notice the search path reference the VAL2
element specifically.
set serveroutput on declare l_json_text varchar2(32767); l_boolean boolean; begin l_json_text := '{"id":1, "val1":"banana", "val2":true}'; l_boolean := json_value(l_json_text, '$.val2' returning boolean); if l_boolean then dbms_output.put_line('val2=true'); else dbms_output.put_line('val2=false'); end if; end; / val2=true PL/SQL procedure successfully completed. SQL>
JSON_VALUE with Predicates
In Oracle database 23c the JSON_QUERY
and JSON_VALUE
functions can include multiple predicates in a single JSON path expression, and use the PASSING clause to support variables.
For more information see:
- Using JSON_VALUE To Instantiate a User-Defined Object-Type or Collection-Type Instance
- Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value
- Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...