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

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

◉ Setup

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

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');

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

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

insert into t1 values (null, null);

commit;

select rownum, col1, col2 from t1;

 ROWNUM COL1 COL2

----------      --------- ----------

         1          ONE       TWO

         2         ONE

         3                         TWO

         4

SQL>

◉ NULL ON NULL and ABSENT ON NULL

In previous releases our only options for null handling during JSON generation were NULL ON NULL and ABSENT ON NULL. We'll use JSON_OBJECT as an example, where the default behaviour is NULL ON NULL.

When using NULL ON NULL, implicitly or explictly, any null values in the data are generated as "null" in the output.

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

When using ABSENT ON NULL any null values in the data result in the corresponding element being removed from the output.

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

DATA

--------------------------------------------------------------------------------

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

{"COL1":"ONE"}

{"COL2":"TWO"}

{}

SQL>

Here's an example with JSON_ARRAY, which uses ABSENT ON NULL by default.

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

◉ EMPTY STRING ON NULL

In Oracle database 23ai we now have the option of using EMPTY STRING ON NULL. In this case null values are presented as empty strings in the output.

Here we see JSON_OBJECT being used with EMPTY STRING ON NULL.

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>

Here is an example of JSON_ARRAY using EMPTY STRING ON NULL.

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

DATA

--------------------------------------------------------------------------------

["ONE"]

["ONE"]

[""]

[""]

SQL>

◉ 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.

Source: oracle-base.com