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