在PostgreSQL中使用json数组的索引

Question

在PostgreSQL中使用json数组的索引

sqlarrayspostgresqlindexingjsonb

5

参考原始stackoverflow问题，我正在尝试在Postgres 9.4中将gin索引应用于数组中对象的键，但结果与第一个回答中所述不符。

能否请您纠正错误？

下面是我所遵循的步骤。

第一部分：创建表和索引。

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);
INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]');
INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

第二部分：查询

SELECT * FROM tracks WHERE artists @> '{"name": "The Dirty Heads"}';
 id | artists 
----+---------
(0 rows)

这个查询没有结果。

我也尝试过使用 jsonb_path_ops GIN 索引。

替代的索引和查询：

DROP INDEX tracks_artists_gin_idx;
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING  gin (artists jsonb_path_ops);
SELECT * FROM tracks WHERE artists @> '{"name": "The Dirty Heads"}';
 id | artists 
----+---------
(0 rows)

- user2512324

1

在你所提到的例子中，我的答案中缺少了数组层。现在已经修复了。@potatosalad的答案提供了解释。 - Erwin Brandstetter

对于未来查看此问题的用户，我发现这个链接https://dev59.com/jovda4cB1Zd3GeqPaH93#30592076展示了如何查询`jsonb`对象数组，类似于上面所述的查询意图。 - ArchNoob

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- potatosalad · Accepted Answer

这个特定的jsonb示例来自原回答，在包含查询的非原始对象周围缺少数组层[]。现已修复。

对于PostgreSQL 9.4.xjsonb包含和存在性所记录的行为如下：

一般原则是包含的对象必须与包含对象匹配，包括结构和数据内容

...

作为一种特殊情况，结构不匹配的数组可以包含一个原始值

这个特殊情况允许我们执行以下操作：

CREATE TABLE tracks (id serial, artistnames jsonb);
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);
INSERT INTO tracks (id, artists) VALUES (1, '["blink-182"]');
INSERT INTO tracks (id, artists) VALUES (2, '["The Dirty Heads", "Louis Richards"]');

我们可以使用以下一般原则进行包含查询：

SELECT * FROM tracks WHERE artistnames @> '["The Dirty Heads"]';
 id |              artistnames              
----+---------------------------------------
  2 | ["The Dirty Heads", "Louis Richards"]
(1 row)

我们也可以使用特殊的例外来查询包含关系，因为数组包含原始类型：

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"';
 id |              artistnames              
----+---------------------------------------
  2 | ["The Dirty Heads", "Louis Richards"]
(1 row)

有4种基本类型可以使数组的包含和存在查询正常工作：

字符串
数字
布尔值
null

由于你在问题中提到的示例涉及嵌套在数组中的对象，因此我们不符合上述特殊情况的要求：

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);
INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]');
INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

我们可以使用以下一般原则来查询包含关系：

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';
 id |                          artists                          
----+-----------------------------------------------------------
  2 | [{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]
(1 row)

对象不被视为原始类型，因此以下包含查询不符合特殊异常的条件，因此无法工作：

SELECT * FROM tracks WHERE artists @> '{"name": "The Dirty Heads"}';
 id | artists 
----+---------
(0 rows)