Query in json in postgres with multiple values

Question

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 ?

can you add the table structure, sample data and expected output to understand better. — Vivek
– Vivek, Commented Jul 17, 2018 at 9:20

klin · Accepted Answer · 2018-07-17 09:25:35Z

2

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)

answered Jul 17, 2018 at 9:25

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Query in json in postgres with multiple values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related