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

Ссылаясь на qaru.site/info/37099/..., я пытаюсь применить индексы gin к ключам в объектах массива в 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)

Ответы

Ответ 1

В этом конкретном jsonb-примере из исходного ответа отсутствовал слой массива [] вокруг не-примитивного объекта для запроса сдерживания. С тех пор оно было исправлено.

Поведение, зарегистрированное для PostgreSQL 9.4.x jsonb Containment and Existence, указывает:

Общий принцип заключается в том, что содержащийся объект должен соответствовать содержащемуся объекту в отношении структуры и содержимого данных

...

В качестве особого исключения из общего принципа, который должны соответствовать структуры, массив может содержать примитивное значение

Специальное исключение позволяет нам сделать следующее:

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 примитивных типа, которые позволяют сдерживать запросы на сдерживание и существование на массивах:

  • строка
  • номер
  • булева
  • NULL

Так как пример, который вы упомянули в своем вопросе, имеет дело с объектами, вложенными внутри массива, мы не можем претендовать на специальное исключение, упомянутое выше:

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)