Глава 2. Базовые возможности JSON

Главы

1. Введение в документы
2. Базовые возможности JSON
3. JSON в таблицах
4. Индексирование JSON
5. Ссылки и ограничения в документах
6. Язык путей JSONPath
7. Отчеты и функции
8. Функции на языке Python
9. Версионирование и архивация документов
10. Релевантный поиск

Содержание

• Главы
• Установка и подключение
• Знакомство с json и jsonb
• Решения до JSON
• Подробнее о JSONb
• Операторы доступа
• Квадратные скобки
• Другие операторы
• Функции json(b)
• JSON как таблица

В этой главе мы перейдем к техническим вещам: познакомимся с типом JSON в Postgres. Мы рассмотрим основные операции над ним и ряд функций, которые облегчат работу. Покажем неочевидные трюки, когда JSON полезен: как прочитать его по частям, как передать с его помощью таблицу и многое другое.

Установка и подключение

Прежде чем начать работу, убедитесь, что у вас установлен сервер Postgres. На момент написания книги его последняя версия – 18.1, но ранние тоже подойдут. Случаи, когда та или иная техника требуют особой версии, оговариваются особо.

Если у вас нет Postgres, то вот как установить его в разных системах. Прежде всего это официальный сайт проекта. Посетите страницу загрузок и укажите операционную систему. Вас направят на другую страницу с ссылкой на установщик.

Компания PostgresPro предлагает графический инсталлятор для Windows. Postgres Pro – это расширенная база данных со своими расширениями и функциями. Наша книга опирается на стандартный Postgres, поэтому Pro-версия тоже подойдет.

Есть и другие способы установки, которые не требуют посещений сайтов. Пользователи Linux семейства Debian и Ubuntu воспользуются менеджером пакетов apt:

apt install postgresql

Похожим образом работает установка в CentOS и Fedora: их пакетный менеджер называется yum. Отличие в том, что пакет postgresql содержит только клиентские утилиты (например psql), а серверная часть вынесена в пакет postgresql-server. Нам понадобится то и другое:

yum install postgresql postgresql-server

Скорее всего, потребуется предварить apt и yum командой sudo.

Пользователям MacOS доступен менеджер пакетов Homebrew:

brew install postgresql@17

Эта команда только установит базу данных, но не запустит ее. Чтобы включить Postgres в фоне, выполните:

brew services start postgresql@17

В числе прочего эта команда добавит Postgres в автозагрузку.

Еще один способ установки под MacOS – утилита Postgres.app с одноименного сайта. Это минимальная графическая оболочка над сервером, чтобы включать и выключать его нажатием кнопки. С помощью Postgres.app легко запустить несколько серверов на разных портах. Приложение доступно в нескольких комплектациях, в том числе с предустановленным расширением PostGIS. Также оно есть в Homebrew Casks:

brew install --cask postgres-unofficial

После установки запустите его как обычное приложение из папки /Applications. Нажмите кнопку “плюс”, чтобы добавить новый сервер. Нажмите Init (первичная подготовка сервера) и Start (его запуск).

Еще один вариант запуска – это средства виртуализации вроде Docker. Если у вас установлен Docker, запустите образ Postgres командой:

docker run --rm -it -p 15432:5432 -e POSTGRES_PASSWORD=postgres postgres:17.4

Обратите внимание на переменную среды POSTGRES_PASSWORD. Образ обязывает нас указать пароль пользователя, чтобы подключиться под ним. Особенности подключения мы рассмотрим чуть ниже.

При первом запуске docker run понадобится время, чтобы скачать образ postgres:17.4 из репозитория Docker. Далее команда запустит сервер в контейнере. На локальной машине он доступен на порту 15432. Имя пользователя, пароль и база данных по умолчанию совпадают: это “postgres”.

Кроме Docker существуют и другие менеджеры контейнеров, например Podman. Еще одна программа называется Orbstack; она разработана специально для MacOS. Ее команды аналогичны Docker, поэтому способ выше сработает и для Orbstack.

Итак, текст ниже предполагает, что вы установили Postgres и готовы работать с ним. Переходим к следующему шагу: подключимся к базе при помощи утилиты psql. Это интерактивная консоль для запросов и управления сервером. Ниже мы разберем особенности подключения.

По умолчанию в Postgres одна база данных под названием postgres и одноименный пользователь, ее владелец. Пароль этого пользователя зависит от того, как прошла установка. Например, инсталлятор для Windows запросит пароль на одном из шагов. В Linux и MacOS пароль либо не задан, либо сгенерирован случайно и неизвестен. Как же тогда подключиться?

Если не указано иное, Postgres доверяет локальным подключениям – трафику из сетей 127.0.0.1/32 (IPv4), ::1/128 (IPv6), а также unix-сокету (только на Linux и MacOS). Для таких соединений пароль не проверяется. Настройки подключения находятся в файле pg_hba.conf и по умолчанию выглядят так:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Смените системного пользователя на postgres и запустите psql:

sudo -u postgres psql

Вы подключитесь к базе postgres под одноименным пользователем, при этом утилита не спросит пароль. По желанию задайте его командой:

\password postgres

Приведем запуск psql с большим числом параметров. Ниже явно заданы хост и порт, что полезно, когда сервер запущен на особом порту, например 15432. Параметр -W обязывает утилиту запросить пароль. Последний параметр – имя базы данных.

psql --port 5432 --host 127.0.0.1 --user postgres -W postgres

Приложение Postgres.app автоматически создает пользователя и базу данных для текущего пользователя; в случае автора это ivan. Для подключения к ней кликните по значку базы, и откроется терминал psql.

Оказавшись в консоли, выполните запрос с версией Postgres. Так мы убедимся, что сервер работает и можно двигаться дальше:

select version();
-- PostgreSQL 17.4 (Postgres.app) on aarch64-apple-darwin23.6.0, compiled by Apple clang version 15.0.0 (clang-1500.3.9.4), 64-bit

Если psql неудобен, опробуйте графические приложения: PGAdmin, DBeaver, DataGrip и другие. Рассматривать их установку мы не будем, потому что во всех случаях она тривиальна: зайти на сайт, скачать дистрибутив и установить его. Далее запустить программу и ввести параметры сервера. Некоторые программы доступны в менеджерах пакетов: apt, yum, brew. Иные из них условно бесплатные, делятся на обычную и премиум-версии, взимают плату по подписке.

Знакомство с json и jsonb

Надеемся, что подключение состоялось, и вы увидели приглашение psql. Выполните два запроса:

select '[1, "test", true]'::json as data;
select '[1, "test", true]'::jsonb as data;

Если оба сработали без ошибок, все готово для будущих упражнений.

Начнем знакомство с JSON со следующего вопроса. Postgres предлагает два типа json: обычный и с частичкой “b” на конце (jsonb). В чем разница между ними и что предпочесть?

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

Тип jsonb означает jsonb binary, то есть двоичный. Он появился позже json и представляет его улучшенную версию. Jsonb хранится по-другому: когда строка поступает в базу, она разбирается на дерево узлов (нод). Схематично дерево выглядит так:

              ┌──────────────────────┬────┐
              │                      │    │
              ▼                      │    │
       ┌────────────┐                │    │
    ┌─▶│type        │                │    │
    │  ├────────────┤                │    │
    │  │numeric     │                │    │
    │  ├────────────┤                │    │
    │  │boolean     │                │    │
    │  ├────────┬───┴───┬──────────┐ │    │
    │  │string  │length │characters│ │    │
    │  ├────────┼───────┼────┬────┬┴───┬────┬────┐
    │  │pairs   │length │key1│key1│key2│key2│....│
    │  ├────────┼───────┴────┴─┬──┴────┴────┴────┘
    │  │array   │JsonbContainer│
    │  ├────────┼──────────────┘
    │  │datetime│       │
    │  └────────┘       │
    │                   │
    │                   │    ┌───────┬─────┬─────┬─────┐
    │                   └───▶│length │item1│item2│.....│
    │                        └───────┴─────┴─────┴─────┘
    │                                   │
    │                                   │
    └───────────────────────────────────┘

В языке Си это набор структур и указателей; структуры объединяются оператором union. Исходный код модуля jsonb доступен на GitHub.

Каждый узел содержит тип значения (число, строка, массив и так далее) и различные поля. В зависимости от типа узла заполнено то или иное поле. Для строк вычисляется длина, чтобы не искать каждый раз нулевой байт. Сложные типы (объекты и массивы) хранятся в виде ссылок на другие узлы.

Обратите внимание на поле datetime. Стандарт JSON не определяет типов даты и времени; с точки зрения парсера это строка или число (формат ISO, эпоха UNIX). Однако потребность в датах столь высока, что ради них Postgres делает исключение. Если текстовое поле может быть приведено к дате, результат записывается в datetime и кэшируется.

Из-за того, что типы json и jsonb устроены по-разному, проявляются особенности в работе с ними.

Тип json быстрее вставляется в базу. Так происходит потому, что он хранится как строка и не требует преобразования в дерево. Если вы пишете в базу много документов, возможно, колонка json будет удачным выбором. Однако на этом преимущества json заканчиваются, и по остальным критериям он проигрывает jsonb.

Тип jsonb, напротив, слегка медленней при вставке, но гораздо быстрее в других операциях. Например, доступ ко вложенному полю примерно в 4 раза быстрее по сравнению с json. Различные функции и операторы, о которых мы будем говорить, тоже производительней, когда имеют дело с jsonb.

Разница в типах отражается на именовании функций. Так, функции json_build_array и jsonb_build_array отличаются буквой b, которая указывает, что во втором случае результат носит тип jsonb. В Postgres множество подобных парных функций.

Вывод этого раздела следующий: предпочитайте двоичный jsonb. Тот факт, что он чуть медленней при вставке, с лихвой окупается скоростью чтения. Многие продвинутые техники для JSON поддерживают только бинарный формат. К ним относится JSON Path, функции семейства json_table и другие. Все это мы рассмотрим в будущих главах.

Решения до JSON

Следующий вопрос в нашей повестке: какие задачи решает JSON? В прошлой главе мы ответили на него: чтобы хранить данные, структура которых неточна или меняется со временем. Рассмотрим эту проблему в техническом ключе.

Задолго до того как Postgres обзавелся типами json(b), программистам нужно было хранить произвольные данные. Мы уже упоминали причины: порой данные слишком сложны, чтобы раскладывать их по таблицами. В ORM и фреймворках задача решалась так: создавали класс JsonField, унаследованный от TextField. У класса переопределяли два метода: условные from_db_value и get_prep_value. Эти методы – посредники между миром SQL и приложением. Первый вызывается, когда значение пришло из базы и может быть преобразовано к типу платформы, например java.time.Instant или datetime в Python. Второй метод выполняет обратное: перед записью в базу приводит значение к его SQL-аналогу.

В случае с JSON первый метод парсит строку в словари и списки, а второй – приводит обратно к строке. Приведем класс JSONFieldна псевдокоде, близком к Python и фреймворку Django:

class JSONField(fields.TextFiled):
    db_type = "jsonb"

    dеf from_db_value(self, value, expression, connection):
        return json.loads(value, **self.decoder_kwargs)

    dеf get_prep_value(self, value):
        return json.dumps(value, **self.encoder_kwargs)

Полная версия класса доступна на GitHub в проекте django-jsonfield. Подобных проектов было множество, каждый предлагал те или иные оптимизациями. Со временем Django обзавелся встроенным JSONField, и потребность в них отпала.

Рассмотрим пример с этим классом. Профиль содержит поле extra_data для различных сведений. Мы извлекаем профиль по коду и пишем в extra_data номера учетных записей в соцсетях.

from django.db import models
import jsonfield

class Profile(models.Model):
    is_active = fields.BooleanField()
    extra_data = jsonfield.JSONField()

profile = Profile.objects.get(id=123)
profile.extra_data["google_id"] = "31152234236"
profile.extra_data["twitter_id"] = "2477313462"
profile.save()

Удобство JSONField в том, что он скрывает тонкости работы с базой. Для программиста поле extra_data – словарь, куда можно записать что угодно и позже получить данные.

Промышленная версия JSONField включает множество оптимизаций. Одна из них в том, чтобы при разборе строки вернуть объект dict с частными методами __setitem__ и __delitem__. С их помощью определяют, изменились ли данные после чтения из базы. Если изменений не было, поле jsonb не участвует в выражении UPDATE. Приведем класс TrackingDict, который отслеживает факт изменения в приватном поле _is_changed:

from collections import UserDict

class TrackingDict(UserDict):

    dеf __init__(self, data):
        super().__init__(data)
        self._is_changed = False

    dеf __delitem__(self, key):
        self._is_changed = True
        super().__delitem__(key)

    dеf __setitem__(self, key, value):
        self._is_changed = True
        self.data[key] = value

data = TrackingDict({"foo": 1})
data._is_changed # False
data["abc"] = 3
data._is_changed # True

Класс JSONField изменяют так, чтобы при чтении из базы он оборачивал результат в TrackingDict, а при сохранении проверял флаг _is_changed. В некоторых случаях это снижает трафик и ускоряет запрос. Например, если у профиля изменили поле is_active, а extra_data не трогали, то метод .save() породит запрос:

update profiles set is_active = true where id = 123

Кажется, что JsonField решил все проблемы: в нем можно хранить что угодно, кодирование протекает без нашего участия. Легко изменить класс так, чтобы вместо JSON использовался другой формат данных, например Message Pack или даже Pickle – двоичный дамп Python. Однако при всех достоинствах у JsonField есть и недостатки, и сейчас мы их рассмотрим.

Главный из них в том, что содержимое читается из базы целиком, неважно какие именно поля нужны. Предположим, JsonField хранит большой документ, и требуется узнать его версию и заголовок. Мы не можем сказать базе: верни подмножество документа с полями version и name. С ее точки зрения колонка – неделимый текст. Документ читается целиком, что увеличивает нагрузку на сервер.

То же самое относится к обновлению документа. Нельзя сказать базе: обнови в документе поля version и name. Нужно прочитать его из базы, декодировать, обновить поля, привести к JSON и записать в базу. Разница между прежним и новым документами всего несколько байтов, но мы передали туда и обратно килобайты.

Ситуация ухудшается, когда нужно изменить много сущностей, например миллион документов. Для каждого придется выполнить шаги: прочитать, распарсить, изменить, сериализовать, записать. Это долго и неэффективно.

Еще один минус – эксклюзивность решения. Класс JsonField, каким бы изящным ни был, существует в рамках конкретной ORM. Если с базой работают из других языков и платформ (а в крупных фирмах это чаще всего так), коллегам придется писать свой JsonField.

Теперь когда видны эти недостатки, становится ясным, зачем поддерживать JSON на уровне базы. При чтении можно затребовать подмножество документа, а не все целиком. Легко обновить отдельные поля, избегая полной перезаписи. Это уменьшает трафик, снимает зависимость от конкретной ORM, упрощает работу с базой данных. Другие полезные свойства JSON требуют больше контекста, и мы рассмотрим их позже.

Подробнее о JSONb

Главное свойство jsonb в том, что он хранит значения разных типов. Их набор и свойства почти полностью совпадают со спецификацией RFC 7159, но в некоторых случаях возможны исключения. Перечислим их:

• строки (string) заданы набором символов и последовательностями \uXXXX. Четыре шестнадцатеричные цифры означают код символа в Unicode. Служебные символы вроде табуляции и переноса строки предваряются обратной косой чертой. После разбора строка хранится как значение с типом text. Желательно, чтобы кодировка JSON совпадала с глобальной кодировкой базы данных (чаще всего это UTF-8), иначе результаты могут быть непредсказуемы.

• Числа (number) не делятся явно на целые (integer, long) и с плавающей запятой (float, double). Вывод числа остается на усмотрение парсера. В Postgres числовые типы приводятся к типу numeric – аналогу BigDecimal в других языках. В отличие от JavaScript, значения NaN и infinity не допускаются.

• Логический тип (boolean) определяет два значения: true и false. Регистр важен: варианты True или TRUE вызовут ошибку синтаксиса.

• Массив (array) – набор других значений, в том числе вложенных массивов или объектов. Может быть пустым. Гарантирует порядок элементов, предлагает доступ по индексу.

• Объект (object): множество пар ключей и значений. Ключи могут быть только строками. Нельзя получить объект, в котором ключи – числовые коды сущностей. Значения могут быть любым JSON-документом, в том числе вложенным объектом или массивом. Объект полезен для группировки полей, например вынести данные о пользователе в подобъект user. В отличие от массива, не гарантирует порядка ключей: при добавлении ключа он может оказаться в любом месте.

• null: тип с одним допустимым значением – null. Означает пустоту, неизвестность, отсутствие значения. Важно: null из мира JSON не имеет ничего общего с NULL в Postgres. Значение null записывается только в нижнем регистре. Два null равны друг другу и считаются полноценным документом. Об особенностях этого типа мы поговорим позже.

Вот как выглядят различные JSON-документы в psql:

select jsonb from (values
    ('1'::jsonb),
    ('[1, "test", true, null]'::jsonb),
    ('{"hello": {"to": ["json"]}}'::jsonb)
) as vals(jsonb);

┌─────────────────────────────┐
│            jsonb            │
├─────────────────────────────┤
│ 1                           │
│ [1, "test", true, null]     │
│ {"hello": {"to": ["json"]}} │
└─────────────────────────────┘

Во всех случаях JSON задан строкой и приводится к нужному типу двойным двоеточием. Если этого не сделать, получим обычный текст. Обратите внимание, что внутри JSON строки заключаются в двойные кавычки. Если в строке находится двойная кавычка (например, знак дюйма), ее предваряют косой чертой. То же самое относится к переносу строки, табуляции и другим служебным символам:

select '{"message": "Display: 6\", weight:\t100 gram"}'::jsonb;

┌────────────────────────────────────────────────┐
│                     jsonb                      │
├────────────────────────────────────────────────┤
│ {"message": "Display: 6\", weight:\t100 gram"} │
└────────────────────────────────────────────────┘

Необязательно вводить JSON в одну строку. Если открыть одинарную кавычку, ввести часть документа и нажать Enter, psql определит, что ввод не завершен и продолжит чтение с клавиатуры. Ввод объекта в несколько строк выглядит так:

select '{
"key_a": 1,
"key_b": 2
}'::jsonb as object;

┌──────────────────────────┐
│          object          │
├──────────────────────────┤
│ {"key_a": 1, "key_b": 2} │
└──────────────────────────┘

Сюда же относится строки, обрамленные двойным долларом. Этот синтаксис открывает строку, в которой символы читаются как есть без какого-либо экранирования. Строка закрывается тоже двойным долларом:

select $$ [
    1,
    2,
    {
        "key_a": 1,
        "key_b": 1
    }
]$$::jsonb;

┌──────────────────────────────────┐
│              jsonb               │
├──────────────────────────────────┤
│ [1, 2, {"key_a": 1, "key_b": 1}] │
└──────────────────────────────────┘

Если точнее, литерал $$ означает именованный тег. В примере выше имя пусто, но можно задать его явно, например $document$. Строка завершается в момент, когда достигнут тот же тег, что и в начале.

А вот еще один трюк для ввода сложного документа. Дайте команду \e, и откроется редактор с предыдущим SQL-запросом. По умолчанию это vim, однако легко указать собственный при помощи переменной среды EDITOR. В ней содержится полный путь к программе и, возможно, параметры. Редактор не обязан быть консольным приложением – он может быть и графическим. В случае автора это emacsclient, который подключается к Emacs, запущенному в режиме сервера:

echo $EDITOR;
/Applications/Emacs.app/Contents/MacOS/bin/emacsclient -c

Сохраните и закройте файл – psql выполнит его содержимое и покажет результат.

Уже на этом этапе видна разница между json и jsonb. Выше мы писали, что jsonb отбрасывает лишние пробелы, переносы строк и повторные ключи в объектах. Поскольку он хранится в виде дерева, эти данные не нужны. Вот как выглядит простой json после ввода – он сохранил начальный вид:

select '  { "key_a": 1, "key_b": 2, "key_a": 3 }  '::json as json;

┌────────────────────────────────────────────┐
│                    json                    │
├────────────────────────────────────────────┤
│   { "key_a": 1, "key_b": 2, "key_a": 3 }   │
└────────────────────────────────────────────┘

Если привести документ к jsonb, он изменится:

select '  { "key_a": 1, "key_b": 2, "key_a": 3 }  '::jsonb as jsonb;

┌──────────────────────────┐
│          jsonb           │
├──────────────────────────┤
│ {"key_a": 3, "key_b": 2} │
└──────────────────────────┘

В документе остался последний ключ key_a со значением 3. Очевидно, он был последним в цикле обхода и поэтому заменил конкурента со значением 1. Это частный случай: Postgres не гарантирует порядка ключей в объекте, и лучше не допускать повторов ключей.

Операторы доступа

Центральное место в работе с JSON – доступ ко вложенным полям. Postgres предлагает несколько операторов для этих нужд, и сейчас мы их рассмотрим.

Оператор -> (одинарная стрелка) извлекает из родительского документа json(b) дочерний. Справа от стрелки находится либо строка (для объекта) либо число (для массива). Результат будет того же типа, что и входной документ – json или jsonb.

Рассмотрим несколько примеров. Получить значение по ключу объекта:

select
    '{"a": 1, "b": 2}'::jsonb -> 'a' as a,
    '{"a": 1, "b": 2}'::jsonb -> 'b' as b,
    '{"a": 1, "b": 2}'::jsonb -> 'c' as c;

┌───┬───┬────────┐
│ a │ b │   c    │
├───┼───┼────────┤
│ 1 │ 2 │ <null> │
└───┴───┴────────┘

Доступ к ключу, которого не существует, не вызовет ошибки – вернется NULL.

Получить первый элемент массива:

select '[10, 20, 30]'::jsonb -> 0 as first;

┌───────┐
│ first │
├───────┤
│ 10    │
└───────┘

Получить последний элемент массива; для этого используют отрицательный индекс:

select '[10, 20, 30]'::jsonb -> -1 as last;

┌──────┐
│ last │
├──────┤
│ 30   │
└──────┘

Для предпоследнего элемента передайте индекс -2 и так далее. Как и в случае с объектом, выход за пределы массива не приводит к ошибке – результат будет NULL. Уточним: нативный NULL Postgres, а не JSON-значение null. Убедимся в этом при помощи оператора IS NULL:

select '{"a": 1, "b": 2}'::jsonb -> 'c' is null as is_null;

┌─────────┐
│ is_null │
├─────────┤
│ t       │
└─────────┘

JSON-значение null, напротив, проверку IS NULL не проходит, потому что не является пустым с точки зрения базы:

select '{"a": 1, "b": 2, "c": null}'::jsonb -> 'c' is null as is_null;

┌─────────┐
│ is_null │
├─────────┤
│ f       │
└─────────┘

Одинарная стрелка всегда возвращает json(b). Кому-то покажется, что выражения

select '{"age": 42}'::jsonb -> 'age';
select '{"name": "John"}'::jsonb -> 'name';

вернули число и строку (типы integer и text в Postgres), однако это не так. В обоих случаях мы получим JSON-документы, которые хранят в себе число и строку. В случае со строкой это видно по двойным кавычкам вокруг нее:

select '{"name": "John"}'::jsonb -> 'name' as name;

┌────────┐
│  name  │
├────────┤
│ "John" │
└────────┘

Как привести документ к нативному типу Postgres, мы узнаем чуть ниже.

Иногда ключ или индекс неизвестны заранее; их передают параметрами. Покажем это на примере: подготовим выражение expr, которое выбирает из объекта поле по ключу. При этом ключ задан параметром:

prepare expr as select '{"test1": "abc", "test2": "xyz"}'::jsonb -> $1::text as result;

Вызов с разным ключом дает разные результаты:

execute expr('test1');

┌────────┐
│ result │
├────────┤
│ "abc"  │
└────────┘

execute expr('test2');

┌────────┐
│ result │
├────────┤
│ "xyz"  │
└────────┘

Ключом может быть колонка таблицы, переменная, результат функции и другие сущности Postgres.

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

select $$ {
  "users": [
    {"id": 1, "name": "John Smith"},
    {"id": 2, "name": "Ivan Petrov"}
  ]
}$$::jsonb -> 'users' -> -1 -> 'name' as name;

┌───────────────┐
│     name      │
├───────────────┤
│ "Ivan Petrov" │
└───────────────┘

Если мы ошиблись в одном из ключей, результат стрелки будет NULL, и весь каскад тоже вернет NULL.

Цепочку одинарных стрелок можно записать короче. Для этого служит оператор #> (уголок), который принимает массив строк и возвращает документ json(b). Вот как выглядит новый запрос:

select $$ {
  "users": [
    {"id": 1, "name": "John Smith"},
    {"id": 2, "name": "Ivan Petrov"}
  ]
}$$::jsonb #> '{users,-1,name}' as name;

Массив задан текстом в фигурных скобках, при этом элементы разделены запятой. Такую запись называют литералом массива. Литерал подразумевает, что элементы известны заранее. Иногда один или несколько элементов неизвестны. Для таких случаев служит конструкция array[], элементы которой могут быть строками, параметрами, переменными или колонками:

select $$ {
  "users": [
    {"id": 1, "name": "John Smith"},
    {"id": 2, "name": "Ivan Petrov"}
  ]
}$$::jsonb #> array['users', '-1', 'name'] as name;

Предположим, что индекс пользователя и поле приходят извне. Это значит, второй и третий элементы массива будут параметрами (в Postgres элементы считаются с единицы). Объявим следующее выражение expr2:

prepare expr2 as select $$ {
  "users": [
    {"id": 1, "name": "John Smith"},
    {"id": 2, "name": "Ivan Petrov"}
  ]
}$$::jsonb #> array['users', $1, $2] as field;

и вызовем его разными способами:

execute expr2('0', 'id');

┌───────┐
│ field │
├───────┤
│ 1     │
└───────┘

execute expr2('-1', 'name');

┌───────────────┐
│     field     │
├───────────────┤
│ "Ivan Petrov" │
└───────────────┘

Следующие два оператора, что мы рассмотрим – это ->> (двойная стрелка) и #>> (двойной уголок). Они аналогичны одинарными операторам -> и #>: принимают либо ключ или индекс, либо массив. Разница в типе результата – он будет нативной строкой Postgres (text).

select $$ {
  "users": [
    {"id": 1, "name": "John Smith"},
    {"id": 2, "name": "Ivan Petrov"}
  ]
}$$::jsonb #>> array['users', '0', 'name'] as name;

┌────────────┐
│   name     │
├────────────┤
│ John Smith │
└────────────┘

В примере выше результат John Smith – строка Postgres, а не JSON. Это видно по отсутствию кавычек.

Двойная стрелка и уголок полезны для вывода типов. Предположим, документ содержит целое и рациональное числа, логический флаг, строку и время в формате ISO:

{
    "points": 150,
    "ratio": 1.05,
    "active": true,
    "message": "You've got new bonus points!",
    "datetime": "2025-12-16T07:36:53Z"
}

Вот как привести их к нативным типам Postgres: int, float, bool, text и timestamptz:

select
    (doc ->> 'points')::int4 as points,
    (doc ->> 'ratio')::float4 as ratio,
    (doc ->> 'active')::bool as is_active,
    (doc ->> 'message') as message,
    (doc ->> 'datetime')::timestamptz as datetime
from (values
    ($${
        "points": 150,
        "ratio": 1.05,
        "active": true,
        "message": "You've got new bonus points!",
        "datetime": "2025-12-16T07:36:53Z"
    }$$::jsonb)
) as vals(doc);

┌────────┬───────┬───────────┬──────────────────────────────┬────────────────────────┐
│ points │ ratio │ is_active │           message            │        datetime        │
├────────┼───────┼───────────┼──────────────────────────────┼────────────────────────┤
│    150 │  1.05 │ t         │ You've got new bonus points! │ 2025-12-16 10:36:53+03 │
└────────┴───────┴───────────┴──────────────────────────────┴────────────────────────┘

Сперва получаем значение в виде текста, а затем приводим к нужному типу. Если мы взяли ключ, которого нет, результат будет null, и вывод типа тоже вернет null. Однако неверное значение вызовет ошибку. Скажем, если в поле datetime находится что-то, отличное от даты (например, 'true', '123' и так далее), получим исключение.

Временные метки заслуживают особого внимания. Как мы упомянули, формально JSON не поддерживает дату и время – их записывают текстом или в виде Unix timestamp. Однако потребность в датах столь высока, что в Postgres для них сделали исключение. Во время разбора строка может приводиться к типу timestamp(tz) и храниться в отдельном поле, чтобы не вычислять его каждый раз.

При работе с JSON храните дату и время в формате ISO 8601, например:

{"created_at": "2025-12-16T07:48:58.104784Z"}

Postgres достаточно гибок: он поддерживает и другие популярные форматы времени. Он учитывает букву T между датой и временем и ее отсутствие, разные записи долей секунд и часовой зоны. Все примеры ниже рабочие:

select ('{"created_at": "2025-12-16T07:48:58.104784Z"}'::jsonb->>'created_at')::timestamptz;
select ('{"created_at": "2025-12-16 07:48:58Z"}'::jsonb->>'created_at')::timestamptz;
select ('{"created_at": "2025-12-16"}'::jsonb->>'created_at')::timestamptz;

Если дата и время записаны в особом формате, воспользуйтесь функцией to_timestamp, которая принимает шаблон:

select to_timestamp(
    $${
      "created_at": "Tue, 15 Nov 1994 12:45:26 GMT"
    }$$::jsonb ->> 'created_at',
    'Dy, DD Mon YYYY HH:MI:SS GMT'
);

┌────────────────────────┐
│      to_timestamp      │
├────────────────────────┤
│ 1994-11-15 00:45:26+03 │
└────────────────────────┘

Однако пользоваться шаблоном каждый раз неудобно. Если все даты отличаются от ISO, поговорите с разработчиками. Как правило, нужно переопределить условный метод .encode JSON-сериализатора для класса Date или аналога.

У оператора ->> есть еще одно свойство. Если применить его к документу, отличному от коллекции, и передать нулевой индекс, получим значение в виде текста. С его помощью json(b) приводят к естественным типам Postgres. Например, к числу:

select ('1'::jsonb ->> 0)::integer;

┌──────┐
│ int4 │
├──────┤
│    1 │
└──────┘

и строке:

select '"message"'::jsonb ->> 0 as text;

┌─────────┐
│ text    │
├─────────┤
│ message │
└─────────┘

Итак, мы рассмотрели одинарные операторы -> и #> (стрелку и уголок) и двойные аналоги: ->> и #>>. Чтобы не перепутать их, составим таблицу:

|               | результат json(b) | результат строка |
|---------------|-------------------|------------------|
| один ключ     | ->                | ->>              |
| массив ключей | #>                | #>>              |

Автор не ожидает, что вы запомните их сразу, поскольку сам путает каждый день. Чаще всего мы будем пользоваться двойными стрелкой и уголком (->> и #>>), потому что заинтересованы в нативных строках. Все четыре оператора еще не раз встретятся нам по ходу книги.

Квадратные скобки

Поля JSON можно читать и другим способом – квадратными скобками. Синтаксис напоминает Python и другие популярные языки. В каждой паре скобок располагается текстовый ключ или числовой индекс (отрицательные индексы отсчитываются с конца массива). Результат будет документом json(b):

select ('{"users": [{"name": "John"}]}'::jsonb)['users'][0]['name'] as name;

Квадратные скобки ведут себя как оператор ->. Можно расположить их несколько друг за другом, чтобы получить цепочку. Значения внутри скобок могут быть параметрами. Подготовим выражение, которое принимает индекс пользователя и имя поля:

prepare expr4 as
select ('{"users": [{"name": "John", "age": 33}]}'::jsonb)['users'][$1::int4][$2::text] as field;

execute expr4(0, 'name');

┌────────┐
│ field  │
├────────┤
│ "John" │
└────────┘

execute expr4(0, 'age');

┌───────┐
│ field │
├───────┤
│ 33    │
└───────┘

У квадратных скобок преимущество: с их помощью не только читают, но и меняют документ. Это выглядит как присваивание:

user['age'] = '30'::jsonb

Выражение справа от равенства должно быть либо json(b), либо строкой, и Postgres приведет ее к json(b) автоматически.

Предположим, колонка attrs содержит характеристики товара. Подготовим таблицу и добавим в нее две позиции:

create table goods (
    id integer primary key,
    title text not null,
    attrs jsonb
);

insert into goods values
    (1, 'T-Shirt', '{"cloth.size": "X", "cloth.color": "red"}'),
    (2, 'Laptop', '{"ram.freq": 2666, "wifi.available": true}');

Нужно добавить ноутбуку пару характеристик: высоту и ширину экрана. Вот как выглядит запрос:

update goods set
    attrs['display.width'] = '30',
    attrs['display.height'] = '20'
where id = 2;

Проверим товар после обновления:

select * from goods where id = 2;

┌────┬────────┬───────────────────────────────────────────────────────────────────────────────────────┐
│ id │ title  │                                         attrs                                         │
├────┼────────┼───────────────────────────────────────────────────────────────────────────────────────┤
│  2 │ Laptop │ {"ram.freq": 2666, "display.width": 30, "display.height": 20, "wifi.available": true} │
└────┴────────┴───────────────────────────────────────────────────────────────────────────────────────┘

Одним запросом мы задали сразу две характеристики; их могло быть и больше. JSON-строки нуждаются в двойных кавычках. Если характеристика содержит название, мы бы назначили ее так:

update goods set
    attrs['processor.family'] = '"ARM M4"'
where id = 2
returning *;

┌────┬────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ id │ title  │                                                        attrs                                                        │
├────┼────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│  2 │ Laptop │ {"ram.freq": 2666, "display.width": 30, "display.height": 20, "wifi.available": true, "processor.family": "ARM M4"} │
└────┴────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Предположим теперь, что характеристики вложены: вместо display.width и display.height мы бы хотели следующий словарь:

{"display": {"width": 30, "height": 20}}

Первый запрос порождает вложенные атрибуты:

update goods set
    attrs['display']['width'] = attrs['display.width'],
    attrs['display']['height'] = attrs['display.height']
where id = 2
returning *;

Прежние (плоские) характеристики можно “занулить” – установить им значение null. Но лучше удалить их из словаря, чтобы не хранить лишние ключи и не порождать путаницу. Для этого служит оператор “минус”. Его и другие операторы мы рассмотрим в следующем разделе.

update goods set
    attrs = attrs - 'display.width' - 'display.height'
where id = 2
returning *;

┌────┬────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ id │ title  │                                                      attrs                                                       │
├────┼────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│  2 │ Laptop │ {"display": {"width": 30, "height": 20}, "ram.freq": 2666, "wifi.available": true, "processor.family": "ARM M4"} │
└────┴────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Другие операторы

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

Кроме стрелок и уголков, что мы рассмотрели, JSON поддерживает оператор вхождения. Выглядит он следующим образом:

jsonb_left @> jsonb_right

(Про себя автор называет оператор @> “мороженым”. Postgres предлагает множество операторов: ||, #>>, #?, @@, @?, ~* и другие. Запомнить их невозможно, поэтому в шутку автор дает им имена. Оператору @> соответствует обратная версия <@, так что приходится уточнять: правое мороженое или левое по направлению рожка.)

Оператор проверяет, что документ слева содержит документ справа. Результат будет логическим флагом. Вхождение поддерживает только jsonb; для обычного json получим ошибку, что оператора с таким типом не существует.

Объясним, что значит “содержит”, потому что это размытый термин. Он в крайней мере зависит от того, какие типы JSON стоят слева и справа от оператора.

С простыми значениями (отличными от коллекций), все просто: они содержат только сами себя:

select '1'::jsonb @> '1'::jsonb; -- true

Null поддается сравнению так же, как строки и числа. Можно передать его в функции и операторы, не рискуя получить NULL во всем выражении.

select 'null'::jsonb @> 'null'::jsonb; -- true

Ситуация усложняется, когда слева от @> находится коллекция, например массив. Если справа скалярное значение, проверяется, что оно входит в массив:

select '[1, 2, 3]'::jsonb @> '1'::jsonb; -- true

Проверка работает только для первого уровня массива. В примере ниже получим ложь, потому что в массиве слева нет единицы:

select '[[1, 4], 2, 3]'::jsonb @> '1'::jsonb; -- false

Если оба значения – массивы, проверяется, что элементы справа входят в массив слева. При этом повторные элементы не играют роли:

select '[1, 2, 3, 4]'::jsonb @> '[1, 3, 1]'::jsonb; -- true

Когда оба значения – объекты, проверяется, что ключи объекта справа содержатся в объекте слева. Затем для каждого ключа берутся левое и правое значения и проверяются рекурсивно. Простой случай, когда один объект входит в другой:

select
  '{"a": 1, "b": 2, "c": 3}'::jsonb
  @>
  '{"c": 3}'::jsonb; -- true

Более сложный пример, когда у документа большая вложенность:

select
  '{"users": [{"id": 1, "name": "John"}]}'::jsonb
  @>
  '{"users": [{"id": 1}]}'::jsonb; -- true

Оператор @> предусматривает больше сценариев, но перечислять их все утомительно. Подчеркнем: если вы пользуетесь им в запросах, не полагайтесь на интуицию – проверяйте конкретный случай. Комбинаций множество, и оператор может сработать не так, как вы ожидали.

Выше мы описали @> – “правое мороженое”, а есть еще и левое: <@. Оператор устроен аналогично, но роли аргументов меняются: проверяется, что левый операнд принадлежит правому.

Еще один оператор ? (вопрос) проверяет, содержит ли объект заданный ключ. Удобство в том, что справа находится не JSON-документ, а обычная строка Postgres:

select '{"users": [{"id": 1}]}'::jsonb ? 'users'; -- true

Оператор - (минус) удаляет поле по ключу или индексу. Пример с объектом:

select '{"a": 1, "b": 2}'::jsonb - 'a';

/* {"b": 2} */

В случае с массивом оператор принимает индекс объекта:

select '["active", "pending", "deleted"]'::jsonb - 2;

/* ["active", "pending"] */

Оператор #- (удаление по пути) удаляет значение из вложенного документа. Его правое значение – массив строк, где ключи чередуются с индексами. Оператор полезен, потому что иные документы достигают пяти и более уровней. Вот как удалить вложенное значение:

select
  '{"users": [{"id": 1, "name": "John"}]}'::jsonb
  #-
  '{users,0,name}';

/* {"users": [{"id": 1}]} */

Возможно, вы помните, что оператор || (палочки) соединяет строки. Когда по обе стороны JSON-документы, он работает по-другому. Оператор объединяет документы, но логика зависит от того, какие типы принимают участие.

Когда оба документы – массивы, они соединяются в один; дубликаты не удаляются:

select
  '[1, 2, 3]'::jsonb || '[3, 4, 5]'::jsonb;

/* [1, 2, 3, 3, 4, 5] */

Если слева – массив, а справа – что-либо отличное от него, оно добавляется к массиву:

select
  '[1, 2, 3]'::jsonb || '"test"'::jsonb;

/* [1, 2, 3, "test"] */

Когда документы – скаляры, образуется массив с обоими значениями:

select
  'true'::jsonb || 'false'::jsonb;

/* [true, false] */

Наиболее интересен случай, когда по обе стороны объекты. Значения правого объекта заменяют значения с таким же ключом левого. В других языках операция называется merge (слияние):

select
  '{"a": 1, "b": 2}'::jsonb || '{"b": 9, "c": 3}'::jsonb;

/* {"a": 1, "b": 9, "c": 3} */

Cлияние полезно при обновлении колонки jsonb. Предположим, в поле attrs записаны характеристики товара, и нужно добавить к ним произвольный набор. Вот как выглядит подобный запрос:

update goods set
    attrs = attrs || $${
    "box.width": "120 mm",
    "box.height": "240 mm",
    "box.weight": "0.9 kilo"
}$$::jsonb
where id = 1
returning *;

Подобное слияние не является глубоким: оно не рекурсивно и учитывает только один уровень. Возможно, вы ожидали: если в одном и том же ключе – массивы, они объединятся. Однако это не так: правый массив заменит левый:

select
  '{"a": 1, "b": [1, 2, 3]}'::jsonb
  ||
  '{"b": [4, 5, 6], "c": 3}'::jsonb;

/* {"a": 1, "b": [4, 5, 6], "c": 3} */

Postgres не предлагает функции глубокого слияния (deep merge), однако ее легко написать самому. Топик “Merging JSONB values in PostgreSQL?” на StackOverflow предлагает около десяти реализаций. Разнообразие вызвано тем, что слияние часто зависит от бизнес-требований.

Операторы @@ (две собаки) и @? (собака с вопросом) служат для работы с языком JSON Path. Он напоминает XPath для XML и в целом делает то же самое: ищет пути внутри документа. JSON Path – обширная тема, которую мы придержим для отдельной главы.

Функции json(b)

На этом мы закончим с операторами JSON и перейдем к функциям. Для демонстрации понадобится таблица и немного данных. Подготовим нескольких пользователей:

create table sample (
    id integer,
    name text,
    job text
);

insert into sample values
  (1, 'Ivan', 'programmer'),
  (2, 'John', 'tester'),
  (3, 'Maria', 'manager');

Функция jsonb_build_array принимает произвольные аргументы и строит массив JSON. Вот как выбрать строки таблицы в виде массивов:

select
    jsonb_build_array(id, name, job) as arr
from
    sample;

┌───────────────────────────┐
│            arr            │
├───────────────────────────┤
│ [1, "Ivan", "programmer"] │
│ [2, "John", "tester"]     │
│ [3, "Maria", "manager"]   │
└───────────────────────────┘

Функция jsonb_build_object возвращает объект – набор ключей и значений. Число аргументов должно быть четным, каждый нечетный элемент должен быть строкой. Ниже мы выбираем строки в виде объектов:

select
    jsonb_build_object('id', id, 'name', name)
    as object
from sample;

┌────────────────────────────┐
│           object           │
├────────────────────────────┤
│ {"id": 1, "name": "Ivan"}  │
│ {"id": 2, "name": "John"}  │
│ {"id": 3, "name": "Maria"} │
└────────────────────────────┘

Функция to_jsonb приводит (почти) любое значение к JSON. Для чисел и строк преобразование очевидно; дата, время и другие сложные типы приводятся к строке. Массивы Postgres становятся массивами JSON. В to_jsonb можно передать запись, и она станет объектом, где ключи – имена колонок. В примере ниже sample – тип строки одноименной таблицы, запись с полями id, name и job.

select to_jsonb(sample) as object
from sample;

┌────────────────────────────────────────────────┐
│                     object                     │
├────────────────────────────────────────────────┤
│ {"id": 1, "job": "programmer", "name": "Ivan"} │
│ {"id": 2, "job": "tester", "name": "John"}     │
│ {"id": 3, "job": "manager", "name": "Maria"}   │
└────────────────────────────────────────────────┘

Функция jsonb_agg, как видно из имени, относится к агрегатным. Она принимает колонку и возвращает JSON-массив значений. Можно передать запись, и в результате получим массив объектов.

Эта возможность требует особого примера. Предположим, есть две сущности (главная и подчиненная), при этом вторая ссылается на первую. Это могут быть писатель и книга, режиссер и фильм и так далее. Требуется получить их в виде дерева как в примере ниже:

{
  "id": 1,
  "name": "Orwell",
  "books": [
    {
      "id": 10,
      "title": "1984",
      "author_id": 1
    },
    {
      "id": 20,
      "title": "The Animal Farm",
      "author_id": 1
    }
  ]
}

Соединим таблицы оператором JOIN. Получим прямоугольную таблицу, где главная сущность дублируется много раз:

select
    author.*, book.*
from author
left join book on book.author_id = author.id;

┌────┬────────────┬────┬───────────┬──────────────────────┐
│ id │    name    │ id │ author_id │        title         │
├────┼────────────┼────┼───────────┼──────────────────────┤
│  1 │ Orwell     │ 10 │         1 │ 1984                 │
│  1 │ Orwell     │ 20 │         1 │ The Animal Farm      │
│  2 │ Dostoevsky │ 30 │         2 │ Crime and Punishment │
│  2 │ Dostoevsky │ 40 │         2 │ The Idiot            │
└────┴────────────┴────┴───────────┴──────────────────────┘

Мы уже упоминали проблему: реляционные базы хорошо обращаются с таблицами, но вложенные структуры даются им плохо. Поэтому данные извлекают в плоском виде, а затем группируют в приложении.

Функция jsonb_agg придает выборке нужный вид на уровне SQL. Результат соединения (JOIN) группируется по главной сущности, а подчиненная агрегируется в массив JSON. Перепишем запрос:

select author.*, jsonb_agg(book) as books from author left join book on book.author_id = author.id group by author.id;

Новый результат:

┌────┬────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ id │    name    │                                                      books                                                      │
├────┼────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│  1 │ Orwell     │ [{"id": 10, "title": "1984", "author_id": 1}, {"id": 20, "title": "The Animal Farm", "author_id": 1}]           │
│  2 │ Dostoevsky │ [{"id": 30, "title": "Crime and Punishment", "author_id": 2}, {"id": 40, "title": "The Idiot", "author_id": 2}] │
└────┴────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

JSON-агрегация снимает необходимость в ORM и постобработке. Данные готовы, и можно отправить их клиенту.

Функция jsonb_array_elements проделывает обратное: расщепляет массив JSON на множество строк. Предположим, мы загружаем в систему авторов и книги, и нам прислали следующий документ:

{
  "id": 1,
  "name": "Orwell",
  "books": [
    {
      "id": 10,
      "title": "1984",
      "author_id": 1
    },
    {
      "id": 20,
      "title": "The Animal Farm",
      "author_id": 1
    }
  ]
}

Для начала приведем его к плоскому виду:

select
    (doc->>'id')::integer as author_id,
    doc->>'name' as author_name,
    (book->>'id')::integer as book_id,
    book->>'title' as book_title
from
    (values ($${
      "id": 1,
      "name": "Orwell",
      "books": [
        {
          "id": 10,
          "title": "1984"
        },
        {
          "id": 20,
          "title": "The Animal Farm"
        }
      ]}$$::jsonb)) as val(doc),
    jsonb_array_elements(doc['books']) as book;

┌───────────┬─────────────┬─────────┬─────────────────┐
│ author_id │ author_name │ book_id │   book_title    │
├───────────┼─────────────┼─────────┼─────────────────┤
│         1 │ Orwell      │      10 │ 1984            │
│         1 │ Orwell      │      20 │ The Animal Farm │
└───────────┴─────────────┴─────────┴─────────────────┘

Автор дублируется столько раз, сколько у него книг. Функция jsonb_array_elements порождает неявное отложенное соединение (LATERAL JOIN). При таком соединении дочерние строки присоединяется к той, из которой порождены.

Усложним пример: пусть в документе несколько авторов. Новый запрос выглядит так:

select
    (author->>'id')::integer as author_id,
    author->>'name' as author_name,
    (book->>'id')::integer as book_id,
    book->>'title' as book_title
from
    jsonb_array_elements($$[
{
  "id": 1,
  "name": "Orwell",
  "books": [
    {"id": 10, "title": "1984"},
    {"id": 20, "title": "The Animal Farm"}
  ]
},
{
  "id": 2,
  "name": "Dostoevsky",
  "books": [
    {"id": 30, "title": "Crime and Punishment", "author_id": 2},
    {"id": 40, "title": "The Idiot", "author_id": 2}
  ]
}
]$$) as author,
    jsonb_array_elements(author['books']) as book;

Функция jsonb_array_elements вызывается дважды по числу уровней. Первый вызов связывает имя author с каждым автором. Затем книги автора разбиваются на элементы book. Результат:

┌───────────┬─────────────┬─────────┬──────────────────────┐
│ author_id │ author_name │ book_id │      book_title      │
├───────────┼─────────────┼─────────┼──────────────────────┤
│         1 │ Orwell      │      10 │ 1984                 │
│         1 │ Orwell      │      20 │ The Animal Farm      │
│         2 │ Dostoevsky  │      30 │ Crime and Punishment │
│         2 │ Dostoevsky  │      40 │ The Idiot            │
└───────────┴─────────────┴─────────┴──────────────────────┘

Поместим результат во временную таблицу import. Для этого предварите запрос выражением:

create temp table import as ...

Предположим теперь, что таблица авторов содержит два поля: код и имя. Вот как вставить в нее авторов без повторов:

insert into authors (id, name)
select distinct on (author_id) author_id, author_name
from import
on conflict (id) do nothing

Обратите внимание на выражение distinct on (author_id) – оно оставляет только одного автора в разрезе их кодов.

То же самое с книгами. Пусть таблица books имеет код, заголовок и ссылку на автора. Вставим в нее книги из временной таблицы (выражение distinct on не понадобится):

insert into books (id, title, author_id)
select book_id, book_title, author_id
from import

Запрос, который строит плоскую проекцию, можно переписать с помощью функции json_table, доступной с версии 17. Она декларативно приводит JSON к таблице. Описание функции займет много места, и мы вернемся к ней в главе про отчетность. Заодно понадобится знание языка JSON Path, который нам тоже предстоит изучить.

JSON как таблица

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

Представьте таблицу товаров. У каждого из них артикул, наименование, цена и другие поля. Подготовим несколько записей:

create table goods (
    sku text primary key,
    title text,
    category text,
    price integer
);

insert into goods values
    ('Asus62HF', 'Laptop',  'computers',  56000),
    ('5236/XXL', 'T-shirt', 'cloth',      2300),
    ('623/A/HS', 'Pencil',  'stationery', 10);

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

[
 {"sku": "5236/XXL", "title": "T-shirt"},
 {"sku": "623/A/HS", "title": "Black pen"},
 {"sku": "UWII5133", "title": "Tea spoon"}
]

Требуется сопоставить товары по артикулу и сообщить, с какой стороны что не найдено. Например, футболка содержится в базе, но не в файле. Ноутбук, наоборот, только у поставщика. Если товар найден в обоих источниках, проверить, совпадают ли их названия.

Задача сводится к тому, чтобы загрузить файл во временную таблицу и соединить с товарами при помощи JOIN. Перенос можно сделать разными способами. Первый – силами приложения: прочитать JSON и выполнить серию INSERT. Второй – преобразовать JSON в CSV и вызвать команду COPY ... FROM.

Недостаток обоих способов в том, что мы выходим за рамки SQL. Мы прибегаем к языку программирования, чтобы обработать файл. Это не фатальный недостаток, но хотелось бы сократить усилия.

Третий способ в следующем. Передадим файл в запрос одним параметром jsonb. На стороне Postgres разобьем его на отдельные записи при помощи функции jsonb_array_elements. Не понадобится код на стороннем языке, потому что все делается силами SQL.

Запишем запрос в виде подготовленного выражения. В нем один параметр $1 – содержимое файла поставщика:

prepare stmt as
with json as (
    select
        item->>'sku' as sku,
        item->>'title' as title
    from
        jsonb_array_elements($1::jsonb) as item
)
select
    db is null as json_only,
    json is null as db_only,
    db is not null as both,
    db.title as db_title,
    json.title as json_title,
    db is not null and db.title <> json.title as title_mismatch
from
    goods as db
full join json on db.sku = json.sku;

Выполним его с набором товаров:

execute stmt($$[
 {"sku": "5236/XXL", "title": "T-shirt"},
 {"sku": "623/A/HS", "title": "Black pen"},
 {"sku": "UWII5133", "title": "Tea spoon"}
]$$);

Изучите результат ниже. Его читают следующим образом: в поле json_only указаны товары, найденные только в файле; db_only – товар найден только в базе, both – в обоих источниках. Колонки db_title и json_title показывают наименования товара в базе и файле. За ними следует признак, совпадают ли они.

┌───────────┬─────────┬──────┬──────────┬────────────┬────────────────┐
│ json_only │ db_only │ both │ db_title │ json_title │ title_mismatch │
├───────────┼─────────┼──────┼──────────┼────────────┼────────────────┤
│ f         │ t       │ t    │ Laptop   │ <null>     │ <null>         │
│ f         │ f       │ t    │ T-shirt  │ T-shirt    │ f              │
│ f         │ f       │ t    │ Pencil   │ Black pen  │ t              │
│ t         │ f       │ f    │ <null>   │ Tea spoon  │ f              │
└───────────┴─────────┴──────┴──────────┴────────────┴────────────────┘

Видим, что ноутбук (Laptop) найден только в базе, потому что у поставщика его нет. Обратная ситуация с чайной ложкой (Tea spoon) – в базе ее нет, найдена у поставщика. Футболка (T-shirt) находится в обоих наборах, при этом названия совпадают. Что касается Pencil и Black pen, то у них одинаковый артикул, но разные названия, что и отражает поле title_mismatch.

Решение не зависит от стороннего языка. Не пришлось парсить JSON и выполнять серию вставок; не понадобилось приводить его к CSV.

Запрос, что мы написали, легко выполнить из любого клиента, в том числе psql. Предположим, файл с товарами называется goods.json и находится в директории ch02. Прочитаем его в переменную goods_file:

\set goods_file `cat ch02/goods.json`

Выражение в кавычках будет выполнено в шелле, и текстовый результат окажется в goods_file. Чтобы сослаться на переменную в psql, используйте синтаксис :'var_name'. Вот как вызвать подготовленное выражение:

execute stmt(:'goods_file');

Уточним: оператор \set и переменные работают только в консоли psql и недоступны в приложении.

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

Итак, мы рассмотрели приемы и техники для работы с JSON. Надеемся, читатель увидел, что этот тип подходит для широкого класса задач. С багажом новых знаний мы переходим к следующему шагу: научимся хранить и обрабатывать большие JSON-документы.