在 PostgreSQL 中使用 json 数组中的索引 [英] Using indexes in json array in PostgreSQL

问题描述

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

Referring to the original stackoverflow question, I am trying to apply gin indexes to keys in objects of an array in Postgres 9.4 but I'm not getting the results as stated in the first answer.

你能更正错误吗?

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

The steps I followed have been written below.

第 1 部分:创建表和索引

Part 1: Creating table and indexes

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_ops GIN 索引.

This query gives empty results.
I also tried to use jsonb_path_ops GIN indexes.

替代索引和查询:

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)

推荐答案

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

This specific jsonb example from the original answer was missing the array layer [] around the non-primitive object for the containment query. It has since been fixed.

PostgreSQL 9.4.x 记录的行为 jsonb遏制和存在状态:

The behavior documented for PostgreSQL 9.4.x jsonb Containment and Existence states:

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

The general principle is that the contained object must match the containing object as to structure and data contents

...

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

As a special exception to the general principle that the structures must match, an array may contain a primitive value

特殊异常允许我们执行以下操作:

The special exception allows us to do the following:

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

我们可以使用一般原则查询是否包含:

We can query for containment using the general principle:

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

我们还可以使用特殊异常查询包含情况，因为数组包含原始类型:

We can also query for containment using the special exception since the array contains primitive types:

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

有 4 种基本类型允许对数组进行包含和存在查询:

There are 4 primitive types that allow containment and existence queries on arrays to work:

1. 字符串
2. 数量
3. 布尔值
4. 空

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

Since the example you mentioned in your question is dealing with objects nested inside an array, we don't qualify for the special exception mentioned above:

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

我们可以使用一般原则查询是否包含:

We can query for containment using the general principle:

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

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

Objects are not considered a primitive type, so the following query for containment does not qualify for the special exception and therefore does not work:

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

这篇关于在 PostgreSQL 中使用 json 数组中的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！