Table: test, JSONB column: content:
create table test (id bigserial primary key, content jsonb);
content contains a list of fixed-length lists:
insert into test values (1, '[["first 1", "second 3"]]');
insert into test values (2, '[["first 1", "second 2"], ["first 2", "second 3"]]');
insert into test values (3, '[["first 1", "second 2"], ["first 1", "second 3"]]');
insert into test values (4, '[["first 2", "second 3"], ["first 1", "second 2"], ["first 1", "second 2"]]');
What's the correct Postgres syntax for a query that returns all rows where
at least one of the content elements satisfies (first element = "first 1") AND (second element ILIKE "%3%")?
That is, in the example above, it should select rows 1 and 3, but not 2 or 4.
Bonus question: what is the most efficient way to do such query (in case there are multiple alternatives)? Does it make sense to look into GIN over JSONB with pg_trgm? (There are millions of rows, the inner string values are typically 10-100 characters long, and each content list contains 0-1000s of lists (most usually 0).)
Thanks!
Inner select expands array elements into separate rows with jsonb_array_elements, outer select does the filtering you want. See SQL Fiddle for live example.
select * from (
select id, jsonb_array_elements(content) as item from test
) as expandedtest
where item->>0 like 'first 1' and item->>1 like '%3%'