CODEWITHSUNDEEP
jsonpostgresql
Shubham
Shubham

Reputation: 25

Query in json in postgres with multiple values

I have json stored [1,2,4,5].I have array s like [1,23,4]. I want to check either value of s array exist in json fields. Other options I may need to store array like ['1','2','4','5'] so i can use?| operator or their is anything else i can do ?

Upvotes: 1

Views: 1490

Answers (1)

klin
klin

Reputation: 121584

There is no ready-to-use function or operator to accomplish that. You can use the function:

create or replace function jsonb_int_array(jsonb)
returns integer[] language sql immutable as $function$
    select array(select jsonb_array_elements_text($1)::int)
$function$;

In the query use the overlap operator:

with my_table(data) as (
values ('[1,2,4,5]'::jsonb)
)

select *
from my_table
where jsonb_int_array(data) && array[1,23,4]

     data     
--------------
 [1, 2, 4, 5]
(1 row)

Upvotes: 2

Related Questions