索引 PostgreSQL JSONB 数组元素

Question

就像标题所说的，如何索引 JSONB 数组？

内容看起来像...

["some_value", "another_value"]

我可以轻松访问以下元素...

SELECT * FROM table WHERE data->>0 = 'some_value';

我创建了一个这样的索引...

CREATE INDEX table_data_idx ON table USING gin ((data) jsonb_path_ops);

当我运行解释时，我仍然看到它按顺序扫描......

索引文本元素数组时缺少什么？

Answer 1

如果要使用索引支持该确切查询，则索引必须如下所示：

CREATE INDEX ON "table" ((data->>0));

如果要使用您拥有的索引，则不能将搜索限制为仅特定数组元素(在您的情况下为第一个)。您可以加快对阵列中任意位置some_value的搜索速度：

SELECT * FROM "table"
WHERE data @> '["some_value"]'::jsonb;

Answer 2

我最终采取了不同的方法。我仍然在使用 JSONB 类型进行搜索时遇到问题，所以我最终将我的列切换到varchar ARRAY

CREATE TABLE table (
data varchar ARRAY NOT NULL
);
CREATE INDEX table_data_idx ON table USING GIN (data);
SELECT * FROM table WHERE data @> '{some_value}';

这有效并且正在使用索引。

---

我认为我对 JSONB 方法的问题是因为该元素实际上嵌套得更远并被视为文本。

即data->'some_key'->>'array_key'->>0

每次我尝试搜索时，我都会收到各种无效的令牌错误和其他类似的事情。

Answer 3

您可能希望创建一个具有主键(或表的其他唯一索引)的具体化视图，并使用 jsonb_array_elements_text 函数将数组字段扩展为文本列：

CREATE MATERIALIZED VIEW table_mv
AS
SELECT DISTINCT table.id, 
jsonb_array_elements_text(data->0) AS array_elem FROM table;

然后，您可以在此实例化视图上创建唯一索引(实例化视图不支持主键)：

CREATE UNIQUE INDEX table_array_idx ON table_mv(id, array_elem);

然后使用主键上与原始表的连接进行查询：

SELECT * FROM table INNER JOIN table_mv 
ON table.id = table_mv.id WHERE table_mv.array_elem = 'some_value';

此查询应使用唯一索引，然后查找原始表的主键，两者都非常快。