Searching an element in top-level json array oracle

Question

I have an array of strings stored in oracle column as a json array in the following format:

["abc", "xyz"]
["cde", "fgh"]
["xyz"]

I have to write a query to check whether a given string is present in any of the arrays in any of the rows. In the above example I would like to see whether "xyz" is present. How should the json path be? I know I can use the 'like' clause but I don't think that is a neat way to do.
Why the query SELECT JSON_QUERY(my_column, '$[*]') FROM my_table is always returning null?

Could it be stored as a LOB? That could be causing the issue. if so, you can try using DBMS_LOB.substr or other DBMS_LOB functions to query and find the data. — Karlomanio
– Karlomanio, Commented Oct 19, 2018 at 21:25

Riyaz · Accepted Answer · 2018-10-20 06:25:34Z

2

I did the following test, this may be what you are looking for:

create table t(json_v varchar2(40))

insert into t values('["abc", "xyz"]');
insert into t values('["cde", "fgh"]');
insert into t values('["xyz"]');

SELECT *
from t, json_table(t.json_v, '$[*]' columns (value PATH '$'))
WHERE value = 'xyz'

Output Result 
JSON_V          value 
["abc", "xyz"]  xyz 
["xyz"]         xyz

Your question two why the query always returns zero as you have to wrap the values see the JSON_QUERY syntax

SELECT JSON_QUERY(json_v, '$[*]' WITH WRAPPER) AS value FROM myTable;

answered Oct 20, 2018 at 6:25

Riyaz

1147 bronze badges

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

2 Comments

falcon Over a year ago

Thank you. I solved it using "select * from my_table where JSON_TEXTCONTAINS(my_json_column, '$', 'xyz')". But this required me to create a json index of type "CTXSYS.CONTEXT". Will this impact performance?

Riyaz Over a year ago

Index will definitely helps you in query performance, use it only if that's the only filter condition in your query. However, if you are filtering result by any other column then index that column and use json_table it will perform better. DOMAIN INDEXES are costly to build and maintain.

Collectives™ on Stack Overflow

Searching an element in top-level json array oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related