Hi,
please see reproduction below. The select statement returns SQL NULL for the supplierName of both rows, even though one is JSON null and the other SQL NULL. How do I distinguish those in the projection so that the result set actually contains the correct data values, aka, JSON null / SQL NULL?
Thanks,
Christoph
CREATE TABLE supplier
(id number NOT NULL
CONSTRAINT supplier_pk PRIMARY KEY,
supplier_doc CLOB
CONSTRAINT supplier_doc_ensure_json CHECK (supplier_doc IS JSON (STRICT WITH UNIQUE KEYS)));
INSERT INTO supplier
VALUES (100,
'{
"supplierId": 100,
"supplierName": null}');
INSERT INTO supplier
VALUES (101,
'{
"supplierId": 101}');
select s.supplier_doc.supplierId, s.supplier_doc.supplerName from supplier s;