Search code examples
sqlpostgresqljsonb

How to filter a JSON array containing any of multiple elements

This is the sample table:

create table leadtime.test (
    id serial primary key,
    name jsonb
)

Data test:

insert into leadtime.test (name)
values ('["abc", "def", "ghi"]');

I want to check if name contains any value in this array '["abc", "132", "456"]'

I have to do this code:

select * from leadtime.test
where (name ? 'abc') or (name ? '132') or (name ? '456');

I was told that multiple OR'ed filters or not optimal for performance.
Is there a better way?

Solution

  • Pass your array of search terms as actual Postgres array and use the |? operator:

    SELECT *
FROM   test
WHERE  name ?| '{abc, def, ghi}';

    The manual:

    jsonb ?| text[] → boolean

    Do any of the strings in the text array exist as top-level keys or array elements?

    Can be supported with a plain GIN index on (name), too.