I have a JSON column and I'm trying to do search on this column by using the CONTAINS function.

Table1

Select *
from   table1
where  CONTAINS(JSON_COL, "I INPATH('/MarketInfo/ABCDEFGHE/OriginalData)')>0)

gives me no result

Select *
from   table1
where  CONTAINS(JSON_COL, "II INPATH('/MarketInfo/ABCDEFGHE/OriginalData)')>0)

gives me correct result.

I checked with json_textcontains and json_value these functions also having same. Results are correct when searched with II but when I search with I results are not displayed. Could you please let me know what is the issue here?

CodePudding user response：

CONTAINS works on XML data; not JSON data.

I checked with json_textcontains and json_value these functions also having same.

There appears to be something strange (possibly a bug, possibly a conversion to a different data type):

CREATE TABLE table1 (
  JSON_COL CLOB CHECK (json_col IS JSON)
);

CREATE INDEX json_index   
  ON table1(json_col)   
  INDEXTYPE IS CTXSYS.CONTEXT   
  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)')

INSERT INTO table1 (JSON_COL)
SELECT '{"MarketInfo": {"ABCDEFGHE": {"data": "ABC", "OriginalData": "X"}}}' FROM DUAL UNION ALL
SELECT '{"MarketInfo": {"ABCDEFGHE": {"data": "ABC", "OriginalData": "I"}}}' FROM DUAL UNION ALL
SELECT '{"MarketInfo": {"ABCDEFGHE": {"data": "ABC", "OriginalData": "II"}}}' FROM DUAL;

COMMIT;

Then if you do:

SELECT json_col
FROM   table1
WHERE  JSON_TEXTCONTAINS(json_col, '$.MarketInfo.ABCDEFGHE.OriginalData', 'I')

or

SELECT json_col
FROM   table1
WHERE  JSON_VALUE(json_col, '$.MarketInfo.ABCDEFGHE.OriginalData') = 'I'

or

SELECT json_col
FROM   table1
       CROSS APPLY JSON_TABLE(
         json_col,
         '$.MarketInfo.ABCDEFGHE'
         COLUMNS (
           OriginalData VARCHAR2(20) PATH '$.OriginalData'
         )
       )
WHERE  OriginalData = 'I'

Then no rows are output. But substituting II or X instead of I they both return the matching row.

You can get the correct result with an explicit cast to a VARCHAR2 data type:

SELECT json_col
FROM   table1
WHERE  CAST(JSON_VALUE(json_col, '$.MarketInfo.ABCDEFGHE.OriginalData') AS VARCHAR2(20)) = 'I'

or

SELECT json_col
FROM   table1
       CROSS APPLY JSON_TABLE(
         json_col,
         '$.MarketInfo.ABCDEFGHE'
         COLUMNS (
           OriginalData VARCHAR2(20) PATH '$.OriginalData'
         )
       )
WHERE  CAST(OriginalData AS VARCHAR2(20)) = 'I'

Which both output:

JSON_COL
{"MarketInfo": {"ABCDEFGHE": {"data": "ABC", "OriginalData": "I"}}}

But there is no obvious reason why this would be required.

db<>fiddle here