Postgres中的复合JSONB数组查询? [英] Composite JSONB array query in Postgres?

问题描述

表:test，JSONB列:content:

Table: test, JSONB column: content:

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

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

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

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

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%")?

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

That is, in the example above, it should select rows 1 and 3, but not 2 or 4.

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

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).)

谢谢！

推荐答案

内部选择可使用jsonb_array_elements将数组元素扩展为单独的行，外部选择可进行所需的过滤.有关实时示例，请参见 SQL小提琴.

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

这篇关于Postgres中的复合JSONB数组查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！