1. 首页
  2. 问题
  3. 在PostgreSQL中的JSON数组中使用索引
小编典典

在PostgreSQL中的JSON数组中使用索引

sql

参考原始的stackoverflow问题，我试图将gin索引应用于Postgres
9.4中数组对象中的键，但没有得到第一个答案中所述的结果。

您能纠正错误吗？

我遵循的步骤已写在下面。

第1部分：创建表和索引

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"}]');

第2部分：查询

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

该查询给出空结果。
我也尝试使用jsonb_path_opsGIN索引。

替代索引和查询：

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)

阅读 408

收藏
2021-05-05

共1个答案

小编典典

原始答案中的这个特定jsonb示例缺少[]用于包含查询的非原始对象周围的数组层。此后已修复。

PostgreSQL 9.4.x
jsonb包含和存在的行为记录为：

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

…

作为结构必须匹配的一般原则的特殊例外，数组可以包含原始值

特殊例外允许我们执行以下操作：

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种原始类型，它们允许对数组进行包含和存在查询工作：

  1. 细绳
  2. 数字
  3. 布尔值
  4. 空值

由于您在问题中提到的示例正在处理嵌套在数组内的对象，因此我们不符合上述特殊例外的条件：

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)
2021-05-05