0

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.

3
  • Please edit your question and show us a sample JSON value that you want to search
    – user1822
    Commented Nov 16, 2022 at 10:01
  • 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. Commented Nov 16, 2022 at 10:49
  • It makes no sense to treat a JSON value like this. Commented Nov 19, 2022 at 21:15

1 Answer 1

0

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^])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.