ILIKE查询，带有postgres中jsonb数组数据的索引

Question

我有一个表，其中有城市作为jsonb列，它有json数组，如下

[{"name":"manchester",..},{"name":"liverpool",....}]

现在我想查询"上的表；name"；列的ILIKE查询。

我试过下面的，但它对我不起作用

select * from data where city->>'name' ILIKE '%man%'

当我知道的时候，我可以通过下面的查询来搜索完全匹配

select * from data where city->>'name' @> 'manchester'

此外，我知道我们可以使用jsonb函数使其成为平面数据和搜索，但它不会使用索引。

有没有用ilike搜索数据的方法，它也使用索引？

Answer 1

索引支持将很困难；为此，一个遵循第一个规范形式的模式将是有益的。

除此之外，您可以使用v12上的JSONPATH语言：

WITH t(c) AS (
SELECT '[{"name":"manchester"},{"name":"liverpool"}]'::jsonb
)
SELECT jsonb_path_exists(
c,
'$.**.name ? (@ like_regex "man" flag "i")'::jsonpath
)
FROM t;
jsonb_path_exists 
═══════════════════
t
(1 row)

Answer 2

您确实应该以不同的方式存储数据。

您可以执行ilike查询"；自然地"；但是没有索引支持，比如：

select * from data where exists (select 1 from jsonb_array_elements(city) f(x) where x->>'name' ILIKE '%man%');

你可以得到这样的索引支持：

create index on data using gin ((city::text) gin_trgm_ops);
select * from data where city::text ilike '%man%';

但它会在键的文本中找到匹配项，以及值，并使用任何存在的不相关键/值。您可以通过创建一个只返回值的函数来解决这个问题，这些值被组合成一个字符串，然后使用函数索引。但随着字符串长度的增加，索引的有效性会降低，因为需要追踪和消除更多的误报。

create or replace function concat_val(jsonb, text) returns text immutable language sql as $$
select string_agg(x->>$2,'   ') from jsonb_array_elements($1) f(x)
$$ parallel safe;
create index on data using gin (concat_val(city,'name')  gin_trgm_ops);
select * from data where concat_val(city,'name') ilike '%man%';

您确实应该以不同的方式存储数据。