Why does the JSON function in ORACLE does not fetch data when used with a search index and NULL values?

Question

The problem I face is about the usage of JSON_VALUE() function in ORACLE with null. I have a table where in column named "JSON_COLUMN" of type BLOB there is a json value which is either {"foo":"111"} either {"notfoo":"111"} either {"foo":null}.

Basically, I've not been able to fetch the rows where the "JSON_COLUMN" column has data with field foo IS NULL or field "foo"="111" with oracle function JSON_VALUE().

I tried this following query :

SELECT * FROM MY_TABLE
WHERE JSON_VALUE(JSON_COLUMN, "$.foo") IS NULL
   OR JSON_VALUE(JSON_COLUMN, "$.foo") = "111"

but this does not fetch the row where the json looks like {"notfoo":"111"} or {"foo":null}.

I also tried each independent side of the OR, each of those works fine. I also tried this query

SELECT * FROM MY_TABLE
WHERE JSON_VALUE(JSON_COLUMN, "$.foo") IS NULL
   OR JSON_VALUE(JSON_COLUMN, "$.foo") IS NOT NULL

and I have the expected results which are all the rows in my table.

Does someone knows why the JSON_VALUE function act like this with the OR ?

EDIT :

After more investigation, the issue is not related to the 'OR' or related to the fact that we miss the on error part. I've been able to reproduce exactly my issue with a minimal database and I missed a point where in my database I've added a search index.

Here is the example where all the results I get are expected, and after the addition of the search index, the results are no more what we expect :

CREATE TABLE DUMMY (COLUMN1 BLOB);

ALTER TABLE
    DUMMY
ADD
    CONSTRAINT DUMMY_CHECK_JSON CHECK (COLUMN1 IS JSON);

INSERT INTO
    DUMMY (column1)
VALUES
    ('{"notfoo":"222"}');

INSERT INTO
    DUMMY (column1)
VALUES
    ('{"foo":"111"}');

INSERT INTO
    DUMMY (column1)
VALUES
    ('{"foo":null}');

select
    COLUMN1
from
    DUMMY
where
    JSON_VALUE(COLUMN1, '$.foo') IS NULL
    OR JSON_VALUE(COLUMN1, '$.foo') = '111';

-- ====> 3 results

CREATE SEARCH INDEX IX_CNT_DUMMY ON DUMMY(column1) FOR JSON PARAMETERS(
    '
        DATAGUIDE OFF
        SEARCH_ON TEXT
        SYNC (EVERY "freq=secondly; interval=1")
        OPTIMIZE (EVERY "freq=weekly; byday=SAT")
    '
);

select
    COLUMN1
from
    DUMMY
where
    JSON_VALUE(COLUMN1, '$.foo') IS NULL
    OR JSON_VALUE(COLUMN1, '$.foo') = '111';

-- ====> only 1 result !

select
    COLUMN1
from
    DUMMY
where
    NOT JSON_EXISTS(COLUMN1, '$.foo')
    OR JSON_VALUE(COLUMN1, '$.foo') = '111';

-- SAME, only one result

drop index IX_CNT_DUMMY;

select
    COLUMN1
from
    DUMMY
where
    JSON_VALUE(COLUMN1, '$.foo') IS NULL
    OR JSON_VALUE(COLUMN1, '$.foo') = '111';

Answer 1

To begin with, in Oracle, you use single quotes to delimit paths in JSON_VALUE or string constants. Second, this has nothing to do with OR. Third, depending on what you want, you can specify to the JSON_xxx functions what to do if the key you are looking for does not exist (the 'null on error' or 'error on error' below)

SQL> SELECT * FROM (select '{"foo":null}' json_column from dual)
  2  WHERE JSON_VALUE(JSON_COLUMN, '$.foo' error on error) IS NULL
  3*    OR JSON_VALUE(JSON_COLUMN, '$.foo'  error on error) = '111';

    JSON_COLUMN
_______________
{"foo":null}


SQL> SELECT * FROM (select '{"foo":"111"}' json_column from dual)
  2  WHERE JSON_VALUE(JSON_COLUMN, '$.foo' error on error) IS NULL
  3*    OR JSON_VALUE(JSON_COLUMN, '$.foo'  error on error) = '111';
SQL> /

     JSON_COLUMN
________________
{"foo":"111"}

SQL> SELECT * FROM (select '{"notfoo":"111"}' json_column from dual)
  2  WHERE JSON_VALUE(JSON_COLUMN, '$.foo' error on error) IS NULL
  3*    OR JSON_VALUE(JSON_COLUMN, '$.foo'  error on error) = '111';
SQL> /

Error starting at line : 1 in command -
SELECT * FROM (select '{"notfoo":"111"}' json_column from dual)
WHERE JSON_VALUE(JSON_COLUMN, '$.foo' error on error) IS NULL
   OR JSON_VALUE(JSON_COLUMN, '$.foo'  error on error) = '111'
Error report -
ORA-40462: JSON path '$.foo' evaluated to no value.

SQL>  SELECT * FROM (select '{"notfoo":"111"}' json_column from dual)
  2  WHERE JSON_VALUE(JSON_COLUMN, '$.foo' null on error) IS NULL
  3*    OR JSON_VALUE(JSON_COLUMN, '$.foo'  ) = '111';
SQL> /

        JSON_COLUMN
___________________
{"notfoo":"111"}

SQL> SELECT * FROM (select '{"notfoo":null}' json_column from dual)
  2  WHERE JSON_VALUE(JSON_COLUMN, '$.foo' null on error) IS NULL
  3*    OR JSON_VALUE(JSON_COLUMN, '$.foo'  ) = '111';
SQL> /

       JSON_COLUMN
__________________
{"notfoo":null}

SQL> SELECT * FROM (select '{"notfoo":null}' json_column from dual)
  2  WHERE JSON_VALUE(JSON_COLUMN, '$.foo' ) IS NULL
  3*    OR JSON_VALUE(JSON_COLUMN, '$.foo'  ) = '111';
SQL> /

       JSON_COLUMN
__________________
{"notfoo":null}

Answer 2

Actually this behavior is a bug from Oracle. You can find it on the oracle support website : https://support.oracle.com/epmos/faces/BugDisplay?id=35457454&parent=SrDetailText&sourceId=3-33252210991

No patch exists yet, they will try to fix it. I'll accept this answer when oracle will fix the bug and deploy the patch.