DBA Hub

📋Steps in this guide1/13

JSON Data Type Constructor Enhancements in Oracle Database 23ai/26ai

The JSON data type constructor can now accept collections, object types and record types as input.

oracle 23configurationintermediate
by OracleDba
52 views
1

JSON Data Type

The JSON data type was introduced in Oracle database 21c. You can find more information about the JSON data type here . The initial version of the JSON data type constructor was quite simplistic. In Oracle database 23ai/26ai the constructor is more flexible, and can now accept collections, object types and record types as input.
2

Associative Array (Index By Table)

In the following example we create an associative array (index by table) populated with three items. We use the JSON constructor to convert the collection into JSON. When we display the contents of the JSON, we see a JSON object containing the three numbered items. We can do a similar thing when the associative array is indexed by a .

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
set serveroutput on
declare
  type t_tab is table of varchar2(10) index by pls_integer;
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab('banana', 'apple', 'orange');

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{"1":"banana","2":"apple","3":"orange"}

PL/SQL procedure successfully completed.

SQL>

set serveroutput on
declare
  type t_tab is table of varchar2(10) index by varchar2(10);
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab('fruit1' => 'banana',
                 'fruit2' => 'apple', 
                 'fruit3' => 'orange');

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{"fruit1":"banana","fruit2":"apple","fruit3":"orange"}

PL/SQL procedure successfully completed.

SQL>
3

Nested Table

In the following example we create a nested table populated with three items. We use the JSON constructor to convert the nested table into JSON. When we display the contents of the JSON, we see a JSON object containing the three numbered items.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set serveroutput on
declare
  type t_tab is table of varchar2(10);
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab('banana', 'apple', 'orange');

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{"1":"banana","2":"apple","3":"orange"}

PL/SQL procedure successfully completed.

SQL>
4

Varrays

In the following example we create a varray populated with three items. We use the JSON constructor to convert the varray into JSON. When we display the contents of the JSON, we see a JSON array containing the three items.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set serveroutput on
declare
  type t_tab is varray(5) of varchar2(10);
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab('banana', 'apple', 'orange');

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
["banana","apple","orange"]

PL/SQL procedure successfully completed.

SQL>
5

Record Types

In the following example we create a record type populated with some data. We use the JSON constructor to convert the record type into JSON. When we display the contents of the JSON, we see a JSON object matching the record type definition.

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
set serveroutput on
declare
  type t_rec is record (
    id   number,
    val1 varchar2(10),
    val2 varchar2(10)
  );

  l_rec  t_rec;
  l_json json;
begin
  l_rec := t_rec(id   => 1,
                 val1 => 'banana', 
                 val2 => 'apple');

  l_json := json(l_rec);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{"ID":1,"VAL1":"banana","VAL2":"apple"}

PL/SQL procedure successfully completed.

SQL>
6

Object Types

In the following example we create an object type, and in our code we create an instance of that object type populated with some data. We use the JSON constructor to convert the object type into JSON. When we display the contents of the JSON, we see a JSON object matching the object type definition.

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
create or replace type t_obj as object (
  id   number,
  val1 varchar2(10),
  val2 varchar2(10)
);
/


set serveroutput on
declare
  l_obj  t_obj;
  l_json json;
begin
  l_obj := t_obj(id   => 1,
                 val1 => 'banana',
                 val2 => 'apple');

  l_json := json(l_obj);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{"ID":1,"VAL1":"banana","VAL2":"apple"}

PL/SQL procedure successfully completed.

SQL>
7

Collections of Record Types and Object Types

We've seen examples of the JSON constructor working with collections, record types and object types. It can also work with collections of record types and object types, as demonstrated below.
8

Associative Array of Record Types

In this example we create a record type, and an associative array based on that record type. We populate the associative array with some data. We use the JSON constructor to convert the associative array into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.

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
set serveroutput on
declare
  type t_rec is record (
    id   number,
    val1 varchar2(10),
    val2 varchar2(10)
  );

  type t_tab is table of t_rec index by pls_integer;
  l_tab  t_tab;
  l_json json;
begin
  l_tab(1) := t_rec(id   => 1,
                    val1 => 'banana1', 
                    val2 => 'apple1');
  l_tab(2) := t_rec(id   => 2,
                    val1 => 'banana2', 
                    val2 => 'apple2');
  l_tab(3) := t_rec(id   => 3,
                    val1 => 'banana3', 
                    val2 => 'apple3');

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{
  "1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
  "2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
  "3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}

PL/SQL procedure successfully completed.

SQL>
9

Associative Array of Object Types

In this example we create an object type, and an associative array based on that object type. We populate the associative array with some data. We use the JSON constructor to convert the associative array into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.

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
create or replace type t_obj as object (
  id   number,
  val1 varchar2(10),
  val2 varchar2(10)
);
/

set serveroutput on
declare
  type t_tab is table of t_obj index by pls_integer;
  l_tab  t_tab;
  l_json json;
begin
  l_tab(1) := t_obj(id   => 1,
                    val1 => 'banana1', 
                    val2 => 'apple1');
  l_tab(2) := t_obj(id   => 2,
                    val1 => 'banana2', 
                    val2 => 'apple2');
  l_tab(3) := t_obj(id   => 3,
                    val1 => 'banana3', 
                    val2 => 'apple3');

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{
  "1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
  "2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
  "3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}

PL/SQL procedure successfully completed.

SQL>
10

Nested Table of Record Types

In this example we create an record type, and a nested table based on that record type. We populate the nested table with some data. We use the JSON constructor to convert the nested table into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.

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
set serveroutput on
declare
  type t_rec is record (
    id   number,
    val1 varchar2(10),
    val2 varchar2(10)
  );

  type t_tab is table of t_rec index by pls_integer;
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab(t_rec(id   => 1,
                       val1 => 'banana1', 
                       val2 => 'apple1'),
                 t_rec(id   => 2,
                       val1 => 'banana2', 
                       val2 => 'apple2'),
                 t_rec(id   => 3,
                       val1 => 'banana3', 
                       val2 => 'apple3'));

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{
  "1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
  "2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
  "3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}

SQL>
11

Nested Table of Object Types

In this example we create an object type, and a nested table based on that object type. We populate the nested table with some data. We use the JSON constructor to convert the nested table into JSON. When we display the contents of the JSON, we see a JSON object containing all the records.

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
create or replace type t_obj as object (
  id   number,
  val1 varchar2(10),
  val2 varchar2(10)
);
/

set serveroutput on
declare
  type t_tab is table of t_obj;
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab(t_obj(id   => 1,
                       val1 => 'banana1', 
                       val2 => 'apple1'),
                 t_obj(id   => 2,
                       val1 => 'banana2', 
                       val2 => 'apple2'),
                 t_obj(id   => 3,
                       val1 => 'banana3', 
                       val2 => 'apple3'));

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
{
  "1":{"ID":1,"VAL1":"banana1","VAL2":"apple1"},
  "2":{"ID":2,"VAL1":"banana2","VAL2":"apple2"},
  "3":{"ID":3,"VAL1":"banana3","VAL2":"apple3"}
}

SQL>
12

Varray of Record Types

In this example we create an record type, and a varray based on that record type. We populate the varray with some data. We use the JSON constructor to convert the varray into JSON. When we display the contents of the JSON, we see a JSON array containing a JSON object for each row in the varray.

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
set serveroutput on
declare
  type t_rec is record (
    id   number,
    val1 varchar2(10),
    val2 varchar2(10)
  );

  type t_tab is varray(5) of t_rec;
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab(t_rec(id   => 1,
                       val1 => 'banana1', 
                       val2 => 'apple1'),
                 t_rec(id   => 2,
                       val1 => 'banana2', 
                       val2 => 'apple2'),
                 t_rec(id   => 3,
                       val1 => 'banana3', 
                       val2 => 'apple3'));

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
[
  {"ID":1,"VAL1":"banana1","VAL2":"apple1"},
  {"ID":2,"VAL1":"banana2","VAL2":"apple2"},
  {"ID":3,"VAL1":"banana3","VAL2":"apple3"}
]

SQL>
13

Varray of Object Types

In this example we create an object type, and a varray based on that object type. We populate the varray with some data. We use the JSON constructor to convert the varray into JSON. When we display the contents of the JSON, we see a JSON array containing a JSON object for each row in the varray. For more information see: Hope this helps. Regards Tim...

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
create or replace type t_obj as object (
  id   number,
  val1 varchar2(10),
  val2 varchar2(10)
);
/


set serveroutput on
declare
  type t_tab is varray(5) of t_obj;
  l_tab  t_tab;
  l_json json;
begin
  l_tab := t_tab(t_obj(id   => 1,
                       val1 => 'banana1', 
                       val2 => 'apple1'),
                 t_obj(id   => 2,
                       val1 => 'banana2', 
                       val2 => 'apple2'),
                 t_obj(id   => 3,
                       val1 => 'banana3', 
                       val2 => 'apple3'));

  l_json := json(l_tab);
  dbms_output.put_line(json_serialize(l_json));
end;
/
[
  {"ID":1,"VAL1":"banana1","VAL2":"apple1"},
  {"ID":2,"VAL1":"banana2","VAL2":"apple2"},
  {"ID":3,"VAL1":"banana3","VAL2":"apple3"}
]

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!