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

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.

Source: oracle-base.com