Oracle: Equivalent sql statement to json_textcontains function-CodePudding

I want to search for specific values in a json object, but can't use json_textcontains.

Table:

|   ID   |    NAME   |                    JSONCOLUMN               | ...
|   1    |    test   |{"key1" : "24", "key2": "35", "key3" : "57"} | ...
|   2    |    test2  |{"key1" : "67", "key2": "78", "key3" : "31"} | ...
|   3    |    test3  |{"key1" : "12", "key2": "35", "key3" : "99"} | ...

Now I have a search term e.g. "35" and want to get all rows that contain this term in the jsoncolumn as value (I don't want to search the keys).

So in the example above rows 1 and 3.

I can achieve that with folowing query:

select * from T t where JSON_TEXTCONTAINS(t.jsoncolumn, '$', '35')

But I cannot use json_textcontains. So I'm looking for an equivalent query.

Edit: Clarified what I want to get from the query.

CodePudding user response：

You can use a LIKE condition or the INSTR() function for the matching.

However, just using this on t.jsoncolumn would find matches in the full json, i.e. also in the key part. To just search in the values, you could extract them first using JSON_QUERY.

Both

SELECT * 
  FROM t 
 WHERE JSON_QUERY(jsoncolumn, '$.*' WITH WRAPPER) LIKE '5%'

and

SELECT * 
  FROM t
 WHERE INSTR(JSON_QUERY(t.jsoncolumn, '$.*' WITH WRAPPER), '35') > 0;

should produce the same result as your JSON_TEXTCONTAINS() example.

See this db<>fiddle.