How to match a column against an array of seach strings?

Question

I have a table jsondocs with the following columns:

id SERIAL
json TEXT

I want to check if the columns json contains some values but not exact match.

For example I can to do something like:

select * from jsondocs where json like '%id:123%'

But for an array containing 100 values. I mean something like (pseudo-code):

select * from jsondocs where json like in ('%id:123%','%id:123234324423243%',...)

I want to do a "like" search from values provided in an array.

• user1822: Please edit your question and show us a sample JSON value that you want to search
• Dimitrios Desyllas: Well iot may be a json but also can be any value from a text field. The is more what I am more interested in.
• Erwin Brandstetter: It makes no sense to treat a JSON value like this.

Answer 1

There are 2 approaches in your problem:

Approach 1

Search as text combining like and any:

select * from jsondocs where json like any(ARRAY['%id:123%','%id:123234324423243%',...])

Approach 2

If you know that the json will have a specific key and the values belong to a specific key in the json field then use this approach:

Assuming that json column contains the following:

{
 "mykey":"somevalue"
}

select * from jsondocs where json::jsonb->>mykey like any(ARRAY['%id:123%','%id:123234324423243%',...])

Generally if you want to do a like from a values in an array use:

mycol like any(ARRAY[^values^])