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

问题描述

参照<一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 jsonb遏制行为与生存状态：

  

总的原则是，以结构和包含的对象必须包含的对象匹配数据内容

  
  

...

  
  

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

特殊例外允许我们做到以下几点：

  CREATE TABLE轨道（ID序列，artistnames jsonb）;
CREATE INDEX ON tracks_artistnames_gin_idx使用杜松子酒（artistnames）的轨道;
INSERT INTO轨道（ID，艺术家）VALUES（1，'[眨眼-182]'）;
INSERT INTO轨道（ID，艺术家）VALUES（2，'[肮脏的头，路易·理查兹]'）;
 

我们可以使用的一般原则为遏制查询：

  SELECT * FROM WHERE曲目@ artistnames GT＆; [肮脏的头]';
 ID | artistnames
---- + ---------------------------------------
  2 | [肮脏的头，路易·理查兹]
（1行）
 

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

  SELECT * FROM WHERE曲目@ artistnames GT＆; '肮脏的头';
 ID | artistnames
---- + ---------------------------------------
  2 | [肮脏的头，路易·理查兹]
（1行）
 

有4原始类型，使遏制和存在疑问的阵列工作：

1. 字符串


2. 号


3. 布尔


4. 空

既然你在你的问题是处理嵌套在一个阵列内的物体所提到的例子中，我们没有资格获得上述特殊的例外：

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

我们可以使用的一般原则为遏制查询：

  SELECT * FROM曲目，艺术家@＆GT; '[{名：肮脏的头}]';
 ID |艺术家
---- + --------------------------------------------- --------------
  2 | [{名：肮脏的头}，{名：路易·理查兹}]
（1行）
 

对象不被视为一个基本类型，因此对于遏制下面的查询不符合的特例，因此不工作：

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

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.

Can you please rectify the error?

The steps I followed have been written below.

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

Part 2: Query

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

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

Alternative index and query:

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)

解决方案

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.

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)

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