jsonb 查询,Postgres中的复合JSONB数组查询？

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%'