参考
original stackoverflow question,我试图将金币索引应用于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_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)
来自
original answer的这个特定的jsonb示例在包含查询的非基本对象周围缺少数组layer [].它已被修复.
Postgresql 9.4.x jsonb Containment and Existence记录的行为指出：
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
特殊例外允许我们执行以下操作：
CREATE TABLE tracks (id serial,artistnames jsonb); CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames); INSERT INTO tracks (id,'["blink-182"]'); INSERT INTO tracks (id,'["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,{"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)
