DBA Hub

📋Steps in this guide1/4

EMPTY STRING ON NULL for JSON Generation in Oracle Database 23ai/26ai

Oracle database 23ai/26ai has introduced the ability to convert nulls to empty strings during JSON generation.

oracle 23configurationintermediate
by OracleDba
16 views
1

Setup

We create and populate a table. Notice each row has a different combination of values with regards to null.

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
conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists t1 purge;

create table t1 (
  col1 VARCHAR2(10),
  col2 VARCHAR2(10)
);

insert into t1
values ('ONE', 'TWO'),
       ('ONE', null),
       (null, 'TWO'),
       (null, null);
commit;


select rownum, col1, col2 from t1;

    ROWNUM COL1       COL2
---------- ---------- ----------
         1 ONE        TWO
         2 ONE
         3            TWO
         4

SQL>
2

NULL ON NULL and ABSENT ON NULL

In previous releases our only options for null handling during JSON generation were and . We'll use as an example, where the default behaviour is . When using , implicitly or explictly, any null values in the data are generated as "null" in the output. When using any null values in the data result in the corresponding element being removed from the output. Here's an example with , which uses by default.

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
-- Implicit - NULL ON NULL.
select json_object(*) as data from t1;

DATA
--------------------------------------------------------------------------------
{"COL1":"ONE","COL2":"TWO"}
{"COL1":"ONE","COL2":null}
{"COL1":null,"COL2":"TWO"}
{"COL1":null,"COL2":null}

SQL>


-- Explicit - NULL ON NULL.
select json_object(* null on null) as data from t1;

DATA
--------------------------------------------------------------------------------
{"COL1":"ONE","COL2":"TWO"}
{"COL1":"ONE","COL2":null}
{"COL1":null,"COL2":"TWO"}
{"COL1":null,"COL2":null}

SQL>

select json_object(* absent on null) as data from t1;

DATA
--------------------------------------------------------------------------------
{"COL1":"ONE","COL2":"TWO"}
{"COL1":"ONE"}
{"COL2":"TWO"}
{}

SQL>

-- Default - ABSENT ON NULL
select json_array(col1) as data from t1;

DATA
--------------------------------------------------------------------------------
["ONE"]
["ONE"]
[]
[]

SQL>


-- Explicit - ABSENT ON NULL
select json_array(col1 absent on null) as data from t1;

DATA
--------------------------------------------------------------------------------
["ONE"]
["ONE"]
[]
[]

SQL>


-- NULL ON NULL
select json_array(col1 null on null) as data from t1;

DATA
--------------------------------------------------------------------------------
["ONE"]
["ONE"]
[null]
[null]

SQL>
3

EMPTY STRING ON NULL

In Oracle database 23ai/26ai we now have the option of using . In this case null values are presented as empty strings in the output. Here we see being used with . Here is an example of using .

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
select json_object(* empty string on null) as data from t1;

DATA
--------------------------------------------------------------------------------
{"COL1":"ONE","COL2":"TWO"}
{"COL1":"ONE","COL2":""}
{"COL1":"","COL2":"TWO"}
{"COL1":"","COL2":""}

SQL>

select json_array(col1 empty string on null) as data from t1;

DATA
--------------------------------------------------------------------------------
["ONE"]
["ONE"]
[""]
[""]

SQL>
4

Thoughts

In Oracle SQL and PL/SQL an empty string and a null are treated the same. This is not the case in other languages, so it's important to give people an option of presenting nulls as empty strings if required. The default null handling of each JSON generation function can differ, so it's important to check the documentation, or always be explicit so other developers know your intentions. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!