Postgresql 是否可以在 postgres jsonb 列中的列表上创建索引

发布于03月14日

我有一个postgres表，其中有一个jsonb列，其中包含我们要查询的列表.Jsonb列数据如下所示:

{"org": 3, "subscriberCodes": ["A555", "A666"]}

我希望能够对表进行查询，以按订户代码进行搜索.

我知道我可以做一个LIKE查询:

SELECT *
FROM permissions.application_settings
WHERE configuration->>'subscriberCodes' LIKE '%A555%'

但这有几个问题，我必须按订户代码触发查询(我试图弄清楚如何执行WHERE IN子句，但不太清楚)，结果是对整个表进行顺序扫描(对于我们拥有的记录数量来说，这不是很好).

我对jsonb还不是很在行.有没有一种方法可以执行WHERE IN子句，并可能在订户代码数组上创建索引，以便不需要顺序扫描？

推荐答案

知道您计划如何查询列、您将使用的which functions and operators以及最有针对性的下标是很好的--这对select an adequate index很有帮助.您可以从为jsonpath:demo使用的整个内容建立索引开始

create index on permissions.application_settings using gin (jsonb_path_ops);

select * from permissions.application_settings
where configuration @? '$.subscriberCodes[*]?(@=="A555")';

expression/functional index可以将索引限制为下标:

create index on permissions.application_settings 
  using gin ((configuration->'subscriberCodes') jsonb_path_ops);

select * from permissions.application_settings
where configuration->'subscriberCodes' @? '$[*]?(@=="A555")';

你也可以坚持non-jsonpath种方法:

create index on permissions.application_settings 
  using gin ((configuration->'subscriberCodes') jsonb_ops);

select * from permissions.application_settings
where configuration->'subscriberCodes' ? 'A555';