JSONB query how to check at-least-one array containment

Question

How do I get the query below to return 'true' to reflect that I need at least one of the value in the array on the right, to be contained in the array on the left?

SELECT   '["fun", "movies"]'::jsonb  @> '["fun", "movies","x"]'::jsonb;

The above returns false (because 'x' is not in the array on the left).

I have tried the ?| operator link

SELECT   '["fun", "movies"]'::jsonb  ?| '["fun", "movies","x"]'::jsonb;

but postgres returns an error

ERROR: operator does not exist: jsonb ?| jsonb LINE 2: SELECT
'["fun", "movies"]'::jsonb ?| '["fun", "movies","x...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 147

Vao Tsun · Accepted Answer · 2018-04-03 07:57:43Z

1

you need to "convert" jsonb array to postgres array first:

t=# SELECT   '["fun", "movies"]'::jsonb  ?| translate('["fun", "movies","x"]'::jsonb::text,'[]','{}')::text[];
 ?column?
----------
 t
(1 row)

answered Apr 3, 2018 at 7:57

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

JSONB query how to check at-least-one array containment

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