8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

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.

Related 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:

Hope this helps. Regards Tim...

Back to the Top.