Запросы и индексы по JSONB

В прошлом уроке мы разобрались, как записка details лежит на диске и как её править. Теперь ищем по запискам. Сначала операторы и язык путей jsonpath на пяти книгах учебной базы магазина, потом каталог на сто тысяч документов и индексы GIN, без которых такой поиск читает всю таблицу.

Записки в этом уроке те же, что в 29-м, только у четырёх книг появились оценки читателей, массив ratings:

ALTER TABLE books ADD COLUMN IF NOT EXISTS details jsonb;

UPDATE books SET details = '{"publisher": "АСТ", "tags": ["классика", "мистика"], "cover": {"type": "твёрдая"}, "ratings": [5, 5, 4]}' WHERE id = 1;
UPDATE books SET details = '{"publisher": "АСТ", "tags": ["фантастика", "классика"], "cover": {"type": "мягкая"}, "ratings": [5, 4]}' WHERE id = 3;
UPDATE books SET details = '{"publisher": "АСТ", "tags": ["фантастика"], "cover": {"type": "мягкая"}}' WHERE id = 4;
UPDATE books SET details = '{"publisher": "Питер", "translator": "Е. Матвеев", "tags": ["алгоритмы", "python"], "cover": {"type": "мягкая"}, "ratings": [5, 5, 5, 4]}' WHERE id = 7;
UPDATE books SET details = '{"publisher": "Русская редакция", "translator": "В. Вдовиченко", "tags": ["программирование", "классика"], "cover": {"type": "твёрдая"}, "ratings": [4, 3]}' WHERE id = 8;

Хотя бы один или все сразу

Оператор ? из 21-го урока проверяет один ключ. Его братья берут список ключей массивом:

• ?| отвечает «да», если есть хотя бы один ключ из списка;
• ?& отвечает «да», если есть все.

SELECT title,
       details ?| array['translator', 'ratings'] AS any_key,
       details ?& array['translator', 'ratings'] AS all_keys
FROM books
WHERE details IS NOT NULL
ORDER BY id;

       title        | any_key | all_keys
--------------------+---------+----------
 Мастер и Маргарита | t       | f
 Пикник на обочине  | t       | f
 Трудно быть богом  | f       | f
 Грокаем алгоритмы  | t       | t
 Совершенный код    | t       | t
(5 rows)

У массива строк эти операторы проверяют элементы, а не ключи. Книги с тегом «мистика» или «python»:

SELECT title FROM books WHERE details -> 'tags' ?| array['мистика', 'python'] ORDER BY id;

       title
--------------------
 Мастер и Маргарита
 Грокаем алгоритмы
(2 rows)

jsonpath: адрес внутри документа

Операторы хороши для простых вопросов. А как спросить «есть ли у книги хоть одна оценка ниже четырёх»? Стрелками и @> такое не выразить.

Представь адрес на конверте: страна, город, улица, дом. Читаешь слева направо и с каждым шагом сужаешь круг. В документе JSON такой адрес записывают на языке jsonpath:

Кусок пути	Что значит
$	весь документ
.tags	ключ tags
[*]	каждый элемент массива
? (@ < 5)	фильтр: оставить значения меньше пяти
@	текущее значение внутри фильтра

Функция jsonb_path_query проходит путь и возвращает каждое найденное значение отдельной строкой:

SELECT title, jsonb_path_query(details, '$.tags[*]') AS tag
FROM books
WHERE id IN (1, 7)
ORDER BY id;

       title        |     tag
--------------------+-------------
 Мастер и Маргарита | "классика"
 Мастер и Маргарита | "мистика"
 Грокаем алгоритмы  | "алгоритмы"
 Грокаем алгоритмы  | "python"
(4 rows)

А jsonb_path_query_array собирает найденное в один массив. Все оценки ниже пятёрки у каждой книги:

SELECT title, jsonb_path_query_array(details, '$.ratings[*] ? (@ < 5)') AS low
FROM books
WHERE details ? 'ratings'
ORDER BY id;

       title        |  low
--------------------+--------
 Мастер и Маргарита | [4]
 Пикник на обочине  | [4]
 Грокаем алгоритмы  | [4]
 Совершенный код    | [4, 3]
(4 rows)

Пути в WHERE: @? и @@

Для фильтров у jsonpath два оператора:

• @? спрашивает «найдёт ли путь хоть что-нибудь»;
• @@ вычисляет путь-условие и возвращает его ответ.

Книги, у которых есть оценка ниже четырёх, и книги в твёрдой обложке:

SELECT title FROM books WHERE details @? '$.ratings[*] ? (@ < 4)';
SELECT title FROM books WHERE details @@ '$.cover.type == "твёрдая"' ORDER BY id;

      title
-----------------
 Совершенный код
(1 row)

       title
--------------------
 Мастер и Маргарита
 Совершенный код
(2 rows)

Строки внутри jsonpath пишутся в двойных кавычках, как в самом JSON, а весь путь в одинарных, как любая строка SQL. Условия складываются через && и ||: путь '$ ? (@.publisher == "АСТ" && exists(@.ratings))' найдёт книги АСТ, у которых есть оценки.

Нестрогий режим

Если пути в документе нет, jsonpath по умолчанию молча возвращает пустоту. Это нестрогий режим lax. Слово strict в начале пути превращает отсутствие ключа в ошибку:

SELECT jsonb_path_query_array('{"a": 1}', 'lax $.b') AS lax_result;
SELECT jsonb_path_query_array('{"a": 1}', 'strict $.b') AS strict_result;

 lax_result
------------
 []
(1 row)

ERROR:  JSON object does not contain key "b"

Для поиска по запискам, где у каждой свой набор ключей, нужен как раз lax. strict пригодится, когда документ обязан иметь форму и ты хочешь об этом узнать.