在PostgreSQL的JSON数组使用索引

问题描述

参照<一href=\"http://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array?rq=1\">original计算器问题，我想杜松子酒指标适用于键在Postgres的9.4数组的对象，但作为第一个答案说我没有得到的结果。

能否请您纠正错误？

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

第1部分：创建表和索引

  CREATE TABLE轨道（ID序列，艺术家jsonb）;
CREATE INDEX ON tracks_artists_gin_idx使用杜松子酒（艺术家）的轨道;
INSERT INTO轨道（ID，艺术家）VALUES（1，'[{名：闪烁-182}]'）;
INSERT INTO轨道（ID，艺术家）VALUES（2，'[{名：肮脏的头}，{名：路易·理查兹}]'）;
 

第2部分：查询

  SELECT * FROM曲目，艺术家@＆GT; {名字：肮脏的头};
 ID |艺术家
---- + ---------
（0行）
 

此查询给出了空的结果。结果
我还试图用 jsonb_path_ops GIN索引。

替代索引和查询：

  DROP INDEX tracks_artists_gin_idx;
CREATE INDEX ON tracks_artistnames_gin_idx使用杜松子酒（艺术家jsonb_path_ops）的轨道;
SELECT * FROM曲目，艺术家@＆GT; {名字：肮脏的头};
 ID |艺术家
---- + ---------
（0行）
 

解决方案

从原来的答案是缺少阵列层 [] 周围的非原始对象遏制查询。它已经被修复。

记载PostgreSQL的9.4.x 状态：

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)

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

1. string
2. number
3. boolean
4. null

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数组使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！