Работа с JSONB

В прошлом уроке кластер про изменение данных закончился. Начинается новый кластер: возможности, за которые PostgreSQL выбирают. Первая из них JSONB, способ хранить в одной колонке данные без жёсткой структуры. Примеры работают на учебной базе магазина.

Анкета и записка

Таблица похожа на анкету: у каждой книги одни и те же поля, название, автор, цена, год. Это работает, пока поля и правда у всех одинаковые.

Но магазин хочет хранить больше. У переводной книги есть переводчик, у русской его нет. У одной книги три тега, у другой ни одного. У подарочного издания цвет обложки, у обычного это никого не волнует. Если заводить под каждую мелочь колонку, таблица обрастёт десятками колонок, и почти все они будут пустыми.

Выход такой: к анкете прикрепить записку, где каждая книга пишет про себя что хочет. Записку пишут в формате JSON, который ты видел в ответах API:

{"publisher": "Питер", "translator": "Е. Матвеев", "tags": ["алгоритмы", "python"]}

А колонка, в которой лежит такая записка, в PostgreSQL получает тип jsonb.

json и jsonb

В PostgreSQL два типа для JSON, и разница между ними видна сразу:

SELECT '{"b": 1, "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;

         as_json          |     as_jsonb
--------------------------+------------------
 {"b": 1, "a": 2, "a": 3} | {"a": 3, "b": 1}
(1 row)

json хранит текст как есть, со всеми повторами и пробелами, и разбирает его заново при каждом обращении. jsonb разбирает JSON один раз при записи и хранит в двоичном виде. Повторный ключ a схлопнулся, побеждает последнее значение, а ключи выстроились в своём порядке. Зато читать jsonb быстро, и только у него есть операторы поиска и индексы.

Бери jsonb. Тип json нужен, только если требуется сохранить исходный текст байт в байт.

Сломанный JSON база не пропустит. Ключи в JSON обязаны стоять в двойных кавычках:

SELECT '{publisher: "АСТ"}'::jsonb;

ERROR:  invalid input syntax for type json
DETAIL:  Token "publisher" is invalid.
CONTEXT:  JSON data, line 1: {publisher...

Кладём записки книгам

Добавим колонку и заполним её у четырёх книг:

ALTER TABLE books ADD COLUMN details jsonb;

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

Значение JSON пишется как обычная строка в одинарных кавычках, PostgreSQL сам превратит её в jsonb. У остальных пяти книг в details остаётся NULL. Записки у всех разные: переводчик есть только у двух, цвет обложки только у одной.