DBA Hub

📋Steps in this guide1/5

JSON_VALUE Function Enhancements in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai the RETURNING clause of the JSON_VALUE function has been enhanced allowing it to convert JSON data to user-defined types.

oracle 23configurationintermediate
by OracleDba
21 views
1

Setup

The examples in this article require the following objects. We create a table and populate it with some JSON data. We create an object type which matches the JSON data.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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"}'),
  (2, '{"ID":2,"VAL1":"banana2","VAL2":"apple2"}'),
  (3, '{"ID":3,"VAL1":"banana3","VAL2":"apple3"}');
commit;

create or replace type t_obj as object (
  id   number,
  val1 varchar2(10),
  val2 varchar2(10)
);
/
2

Using JSON_VALUE to Instantiate a User-Defined Object Type

In Oracle 23ai/26ai the function includes a clause, which allows us to convert JSON data to a user-defined type. In the following example we use the 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 object type in the clause. We can see the object type has been instantiated based on the JSON in the specified row.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
select json_value(data, '$' returning t_obj) as data
from   t1
where  id = 1;

DATA(ID, VAL1, VAL2)
--------------------------------------------------------------------------------
T_OBJ(1, 'banana1', 'apple1')

SQL>
3

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. This means we can populate a collection of this object type. In the following example we create a nested table type based on the 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. We repeat the previous example, but this time use a varray, rather than a nested table. We repeat the previous example, but this time use an associative array (index by table).

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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>

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>

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>

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>
4

Using JSON_VALUE to Instantiate a Boolean Type

The clause can also be used to converts JSON data to built-in types. In the following example we use the function to convert a JSON Boolean into a PL/SQL Boolean type. Notice the search path reference the element specifically.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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>
5

JSON_VALUE with Predicates

In Oracle database 23ai/26ai the and functions can include multiple predicates in a single JSON path expression, and use the PASSING clause to support variables. - Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23ai/26ai For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!