postgresql - Postgres 中的复合 JSONB 数组查询？

Question

表:测试，JSONB列:内容:

create table test (id bigserial primary key, content jsonb);

content 包含固定长度列表的列表:

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"]]');

返回所有行的查询的正确 Postgres 语法是什么至少有一个 content 元素满足 (first element = "first 1") AND (second element ILIKE "%3%")?

也就是说，在上面的示例中，它应该选择第 1 行和第 3 行，而不是第 2 行或第 4 行。

奖励问题:执行此类查询的最有效方法是什么(如果有多种选择)？使用 pg_trgm 通过 JSONB 查看 GIN 是否有意义？ (有数百万行，内部字符串值通常为 10-100 个字符长，每个 content 列表包含 0-1000 个列表(通常为 0)。)

谢谢!

Answer 1

您应该拆分顶级数组并从那里检查元素:

select distinct id, content
FROM test
JOIN lateral (
    select elems 
    FROM jsonb_array_elements(content) jae(elems)
) all_arrays ON TRUE
WHERE elems ->> 0 = 'first 1'
and elems ->> 1 ilike '%3%'
ORDER BY 1;

至于最好的方法，这在很大程度上取决于您的实际数据 - 有多少行，这些 jsonb 结构有多大等。不过，一般来说，像 ilike '%3%' 这样的搜索会有所帮助来自基于 pg_trgm 的索引，因为它们不能使用传统的 btree 索引。

编辑:@Abelisto 在评论中的查询更好，因为它应该更高效，尤其是当内容可以包含 1000 多个元素时:

select * from test 
where exists 
  (select 1 
   from jsonb_array_elements(content) jae(elems) 
   where elems ->> 0 = 'first 1' 
   and elems ->> 1 ilike '%3%'
  );