Использование индексов в массиве json в PostgreSQL

Question

Обращаясь к исходному вопросу stackru, я пытаюсь применить индексы джина к ключам в объектах массива в 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 Джин индексы.

Альтернативный индекс и запрос:

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)

Answer 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)