假设我们需要检查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列中的所有字段不是一个选项,因为它们对于每种类型的记录都有所不同,并且会创建一大组索引(并非每行都有相同的键集).
题
如果您知道只需要查询几个已知密钥,那么您只需索引这些表达式即可.
这是一个太简单但自我解释的例子：
create table foo as SELECT '{"col1": "somevalue","col3": "2016-01-01","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');
NOTE: This is only an example: If you perform an explain over the prevIoUs select,you will see that postgres actually performs a
sequential scan instead of using the index. But this is because we are
testing over a table with a single row,which is not the usual. But
I’m sure you could test it with a bigger table
