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?
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
TRIM) or go ahead with this.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":""}
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" : "" } |
