Oracle: Equivalent sql statement to json_textcontains function

Question

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.

But... json_textcontains doesn't produce any output, it is just a condition. It doesn't extract any information from the JSON data. — astentx
– astentx, Commented Aug 16, 2022 at 13:29
Yeah, and the question is, is there another sql condition, which can search in json data. The above sql statement gives me the rows i want, but i can't use the json_textcontains in my query. So an equivalent replacement of the json_textcontains in the where clause. — Tennie
– Tennie, Commented Aug 16, 2022 at 13:34

buddemat · Accepted Answer · 2022-08-16 14:52:29Z

1

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 '%35%'

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.

edited Aug 16, 2022 at 14:52

answered Aug 16, 2022 at 13:45

buddemat

5,33016 gold badges37 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tennie Over a year ago

I think we tried to use 'like' already, but this includes the keys and there is overlapping naming between keys and values, so i get rows where the key matches but not the value. Or am I wrong here?

buddemat Over a year ago

No, you are right, this answer will also match in those cases, whereas json_textcontains will not. Before your edit, I did not get the question right.

Tennie Over a year ago

Ok, yeah i tried to clarify it in the edit, sorry. Do you think what I'm looking for is possible?

buddemat Over a year ago

You could use JSON_QUERY. I've updated my answer.

Tennie Over a year ago

Ok, i got it to work with a few changes, i needed to use regexp_like, because i have many different conditions to match not just numbers like in my example. But your answer helped me. Thanks!

|

Collectives™ on Stack Overflow

Oracle: Equivalent sql statement to json_textcontains function

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related