Using CONTAINS to find JSON key and value

143 views Asked by Stile At 29 September 2022 at 14:28

I am trying to figure out best way to search for certain key value in json. I created context index on column and I am querying where with contains:

SELECT 
    d.id, 
    d.value
FROM
    OD.DOKUMENT d 
WHERE 
    CONTAINS (d.VALUE, '"key":"123424') > 0

However result is not as expected. I can't figure out how Oracle searches values, clearly it is not similar to standard

LIKE %"key":"123424%

Is there a better way to accomplish that? I can't confirm it in documentation however i suppose that oracle is looking for "key" as well as "123424".

Unfortunately I am not able to upgrade my database version from 12.1.0 to 12.2 (where json is supported).

TechQA.