假设我们需要检查jsonb列是否包含与任何值中的子字符串匹配的特定值(非嵌套,仅第一级)。
如何有效地优化查询以搜索整个JSONB
列(这意味着每个键)以查找值?
在转换为文本的 jsonb 数据类型上执行ILIKE %val%
是否有一些好的替代方案?
jsonb_each_text(jsonb_column) ILIKE '%val%'
例如,请考虑以下数据:
SELECT
'{
"col1": "somevalue",
"col2": 5.5,
"col3": 2016-01-01,
"col4": "othervalue",
"col5": "yet_another_value"
}'::JSONB
当需要在包含 jsonb 列中不同行的不同键配置的记录中搜索模式%val%
时,您将如何优化这样的查询?
我知道使用前后符号搜索%
效率低下,因此寻找更好的方法,但很难找到。此外,显式索引 json 列中的所有字段不是一个选项,因为它们因每种类型的记录而异,并且会创建大量索引(并非每一行都有相同的键集)。
问题
除了将每个键值对提取到文本并执行 ILIKE/POSIX 搜索之外,是否有更好的替代方法?
如果您知道只需要查询几个已知键,则可以简单地为这些表达式编制索引。
这是一个太简单但可以自我解释的例子:
create table foo as SELECT '{"col1": "somevalue", "col2": 5.5, "col3": "2016-01-01", "col4": "othervalue", "col5": "yet_another_value"}'::JSONB as bar;
create index pickfoo1 on foo ((bar #>> '{col1}'));
create index pickfoo2 on foo ((bar #>> '{col2}'));
这是基本思想,即使它对 ilike 查询没有用,但您可以做更多的事情(取决于您的需求)。
例如:如果您只需要不区分大小写的匹配,则只需执行以下操作:
-- Create index over lowered value:
create index pickfoo1 on foo (lower(bar #>> '{col1}'));
create index pickfoo2 on foo (lower(bar #>> '{col2}'));
-- Check that it matches:
select * from foo where lower(bar #>> '{col1}') = lower('soMEvaLUe');
注意:这只是一个例子:如果你对上一个选择执行解释,你会看到 postgres 实际上执行了一个顺序扫描,而不是使用索引。但这是因为我们是在具有单行的表上进行测试,这并不常见。但我相信你可以用更大的桌子测试它;-)
对于巨大的表,如果 firt wilcard 没有出现在字符串的开头,即使是像查询也应该受益于索引(但这不是 jsonb 的问题,而是 btree 索引本身的问题)。
如果您需要优化查询,例如:
select * from foo where bar #>> '{col1}' ilike '%MEvaL%';
。那么您应该考虑改用 GIN 或 GIST 索引。