Skip to Main Content
  • Questions
  • SQL NULL and JSON null. To be the same or not to be !?

Breadcrumb

May 4th

Question and Answer

Thanks for the question.

Asked: January 07, 2019 - 8:50 pm UTC

Last updated: January 07, 2019 - 8:50 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hello Beda,

I have a question regarding keys having a "null" value in JSON.

The JSON documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-data.html#GUID-FBC22D72-AA64-4B0A-92A2-837B32902E2C

says the following:

Note:
A JSON value of null is a value as far as SQL is concerned.
It is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data).
In particular, SQL condition IS NULL returns false for a JSON null value, and SQL condition IS NOT NULL returns true.


However, the following simple example seems to contradict this:

create table t (id number, json_col varchar(32767) check (json_col is json))
/

insert into t values (1, '{"Key1":"A", "Key2":null}')
/

commit
/


Both the following queries use an "IS NULL" predicate and they both return the record:

select * from t mytab
where mytab.json_col.Key2 is null
/

ID JSON_COL
----------------
1 {
"Key1" : "A",
"Key2" : null
}


select * from (
select t.*, json_value(json_col, '$.Key2') val
from t
)
where val is null
/

ID JSON_COL VAL
---------------------------
1 { -
"Key1" : "A",
"Key2" : null
}


Can you please clarify this issue ?

Thanks a lot & Best Regards,
Iudith Mentzel

and we said...

Hi Iudith,

thanks for your question and sorry for my late reply.

We basically deal with two value domains: the domain of values in JSON land (which have strings, Booleans, objects, arrays and null) and the domain of Oracle's SQL (which unlike ANSI SQL  has not Boolean, for example).

The main message in the documentation you cite is that a 'null' value in JSON land is not the same as a NULL value in SQL land. But when you invoke JSON_VALUE (or the simplified dot notation) then the selected value(s) from the JSON domain need to be converted to one value in the SQL domain. We map the json null to a SQL NULL in this case. Which kind of looks like that JSON null and SQL NULL are the same, but they're not. They're just mapped.

There are cases when the difference is more obvious. For instance, if you use a path predicate and want to compare a value with null: JSON_EXISTS (..., '$.address?(@.city == null)' ).

Here the comparison is done with the quality symbol (like == true or == "x") and not using IS NULL. Also if you pass in a SQL bind variable which is NULL it will not be true. Also, in Oracle the empty string '' is unfortunately the same as a VARCHAR2 NULL value which complicates things further.

 

select 1 from dual where json_exists('{a:null}', '$?(@.a == $B)' passing NULL as b)
              *
ERROR at line 1:
ORA-40576: Invalid use of bind variable in SQL/JSON path.


SQL> select 1 from dual where json_exists('{a:null}', '$?(@.a == $B)' passing '' as b);

no rows selected

SQL> select 1 from dual where json_exists('{a:""}', '$?(@.a == $B)' passing '' as b);

     1
----------
     1

select 1 from dual where json_exists('{a:null}', '$?(@.a == null)' );

     1
----------
     1

 


 


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database