CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle12coracle18c
Kinjan Bhavsar
Kinjan Bhavsar

Reputation: 1449

How to put empty string in JSON_OBJECT_T object?

I am trying to add an empty string to a JSON_OBJECT_T using the following code but I am getting null in value and not empty string.

DECLARE
   V_OBJ JSON_OBJECT_T;
BEGIN
   V_OBJ := JSON_OBJECT_T();
   V_OBJ.PUT('customerAccRef','');
   DBMS_OUTPUT.PUT_LINE(V_OBJ.stringify);
END;

When I do this, I am getting the following json

{"customerAccRef":null}

and I want output as below

{"customerAccRef":""}

Can someone suggest what change I need to do to pass an empty string?

Upvotes: 3

Views: 6233

Answers (3)

MT0
MT0

Reputation: 168406

In Oracle, an empty string and NULL are identical. However, an empty CLOB is not identical to NULL so you can use:

DECLARE
   V_OBJ JSON_OBJECT_T;
BEGIN
   V_OBJ := JSON_OBJECT_T();
   V_OBJ.PUT('customerAccRef',EMPTY_CLOB());
   DBMS_OUTPUT.PUT_LINE(V_OBJ.stringify);
END;
/

Which outputs:

{"customerAccRef":""}

You can also use it in the SQL JSON_OBJECT function (as none of the other solutions appear to work in SQL, even if they do work in PL/SQL):

SELECT JSON_SERIALIZE(
         JSON_OBJECT(
           KEY 'emptyString' VALUE '',
           KEY 'toCharNull'  VALUE TO_CHAR(NULL),
           KEY 'trimSpace'   VALUE TRIM(' '),
           KEY 'emptyClob'   VALUE EMPTY_CLOB()
         )
         PRETTY
       ) AS value
FROM   DUAL

Which outputs:

VALUE
{
  "emptyString" : null,
  "toCharNull" : null,
  "trimSpace" : null,
  "emptyClob" : ""
}

fiddle

Upvotes: 0

FCA69
FCA69

Reputation: 311

In 19c, using put(..., '') you get an empty string. Using put_null, or put(..., to_char(null)), put(..., <unitializedString>) you get the "real" null value.

DECLARE
   V_OBJ JSON_OBJECT_T;
   V_STRING VARCHAR2(20);
BEGIN
   V_OBJ := JSON_OBJECT_T();
   V_OBJ.PUT('doubleSingleQuotes','');
   V_OBJ.PUT('toCharNull',to_char(null));
   V_OBJ.PUT('uninitializedStringVariable',v_string);
   V_OBJ.PUT_null('null');
   V_OBJ.PUT('trimSpace', trim(' '));
   DBMS_OUTPUT.PUT_LINE(V_OBJ.stringify);
END;

... gives :

{"doubleSingleQuotes":"","toCharNull":null,"uninitializedStringVariable":null,"null":null,"trimSpace":""}

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

The reason it is happening is due to the fact that Oracle internally changes empty string to NULL values. It is due to some legacy reason and you may read this answer to know the history.

I couldn't find anywhere in the JSON documentation with an option to bypass this particular problem myself, although I'd be glad if someone could find it.

As a workaround to your problem, you could use TRIM function to convert a single space to blank string.

V_OBJ.PUT('customerAccRef' , TRIM(' '));

which gives

{"customerAccRef":""}

This seems to work both in Oracle 12.2 version; I tested in my local machine and in Oracle 18c : DEMO, as well as in 19c (LiveSQL online)

A point to also note here is that a simple select TRIM(' ') from dual always returns NULL, which is surprising and luckily for you, it works as expected with JSONs

Upvotes: 5

Related Questions