Как работать с json в Postgresql?

По моему мнению, PostgreSQl — наиболее универсальная база данных, если нет каких-то специфических требований, то лучше использовать именно её. Какое-то время назад, на волнах хайпа, MongoDB стремительно вырывалась вперёд, но уже много лет и PostgreSQL отлично умеет работать с объектами произвольной структуры, оставляя все возможность настоящей СУБД.

json — самый популярный, на данный момент, формат сериализации данных, состоящий из пар ключ-значение. Является нативным для Javascript (валидный json может быть вставлен в js код,без каких-либо изменений) и поддерживается абсолютно всеми языками программирования. Полюбился разработчиками он тем, что легко читается, весьма лаконичный, в сравнении с XML.

PostgreSQL нативно поддерживает json с версии 9.2 (очень давно). И до этого у постгри был тип данных для пар ключ-значение, но не настолько гибкий и с куда менее полным функционалом. СЕйчас это мощнейший и удобнейший инструмент. Такая мини-nosql база данных внутри реляционной бд.

Давайте начнём с создания новой таблицы. Мы создадим таблицу для небольшого интернет-магазина, в которой будем хранить информацию о том, кто, что и сколько заказал.

CREATE TABLE orders
(
    id   serial NOT NULL PRIMARY KEY,
    data json  NOT NULL
);

Таблица orders состоит из двух колонок:

• id— первичный ключ таблицы, мы в основном будем выбирать значения из таблицы по нему
• data — хранит в себе данные в формате json

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

# запускаем Postgresql внутри докера
# --rm - после остановки контейнера, его содержимое удалится
# --name mypg - задаем имя контейнера
# -e POSTGRES_PASSWORD=mypg - задаем пароль для бд через переменную окружения
# -d - бд запустится в бэкграунде
# --network mypg - контенер будет доступ изнутри виртуальной сети mypg
# -p 5432:5432 - пробрасываем порт постгри на хостовую машину
docker run --rm --name mypg -e POSTGRES_PASSWORD=mypg -d --network mypg -p 5432:5432 postgres:alpine

# запускаем psql внутри докера и подключаемся к созданной ранее бд
docker run -it --rm --network mypg -e PGPASSWORD=mypg postgres:alpine psql -h mypg -U postgres

Я люблю иметь все локально, но не засорять свою систему разным, поэтому именно так. Но вы можете позапускать запросы в своей локальной бд или даже онлайн, к примеру, на dbfiddle.

Создание

Пример инсерта:

insert into orders (data)
values (
           '{ "customer": "John Doe", "items": {"product": "Beer","qty": 3}}'
       );

На уровне синтаксиса вставка ничем не отличается от вставки строки (ну, за исключением того, что в строке json). Мы вставили в таблицу информацию о том, что Джон прикупил шесть бутылок пива.

Обратите внимание, если колонка объявлена с типом json, вставить в неё можно только валидный json, иначе получим ошибку invalid input syntax for type json.

Чтобы дальше было интереснее, вставим ещё записей:

INSERT INTO orders (data)
VALUES (
           '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
       ),
       (
           '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
       ),
       (
           '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
       );

Чтение

Чтение части объектов

Для начала давайте просто вы́читаем всё из таблицы:

postgres=# select * from orders;
 id |                                  data
----+-------------------------------------------------------------------------
  1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 3}}
  2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
  3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
  4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(4 rows)

Как видим, у нас всё вставилось и выглядит как джейсончик.

В постгре есть два нативных оператора -> и ->> для доставания данных изнутри объектов.

• Оператор -> возвращает поле по его ключу (как json-объект)
• Оператор ->> тоже (но как строку)

Давайте получим все имена клиентов:

postgres=# select data->'customer' as customer from orders;
    customer
----------------
 "John Doe"
 "Lily Bush"
 "Josh William"
 "Mary Clark"
(4 rows)

И то же самое со вторым оператором:

postgres=# select data->>'customer' as customer from orders;
   customer
--------------
 John Doe
 Lily Bush
 Josh William
 Mary Clark
(4 rows)

Разница, надеюсь, видна?

-> возвращает объект, поэтому его можно объединять с ним же, чтобы получить вложенное значение, или с ->>. Для примера, давайте получим список продуктов:

postgres=# select data->'items'->>'product' as product from orders order by product;
  product
-----------
 Beer
 Diaper
 Toy Car
 Toy Train
(4 rows)

data->'items' возвращает вложенные объекты с ключом items, и потом ->>'product' выбирает из каждого объекта значение с ключом product, преобразуя его к строке.

Использование JSON-операторов в WHERE

Те же самые операторы можно использовать и совместно с оператором WHERE. Для примера, найдём того, кто прикупил пивка:

postgres=# select data->>'customer' as customer from orders where data->'items'->>'product' = 'Beer';
 customer
----------
 John Doe
(1 row)

JSON-объекты можно кастовать к другим типам (PG всё-таки внимательно относится к типам, и 2 != '2', иначе получите HINT: No operator matches the given name and argument types. You might need to add explicit type casts.). Для примера, найдём, какой продукт купили в количестве двух штук:

SELECT data ->> 'customer'           AS customer,
       data -> 'items' ->> 'product' AS product
FROM orders
WHERE (data -> 'items' ->> 'qty')::int = 2;

------------+-----------
  customer  |  product
------------+-----------
 Mary Clark | Toy Train
(1 row)

Применение функций агрегирования

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

select min((data -> 'items' ->> 'qty')::int),
       max((data -> 'items' ->> 'qty')::int),
       avg((data -> 'items' ->> 'qty')::int),
       sum((data -> 'items' ->> 'qty')::int)
from orders;

-----+-----+--------------------+-----
min | max | avg | sum
-----+-----+--------------------+-----
1 | 24 | 7.5000000000000000 | 30
(1 row)

Функции для работы с JSON

Но этих операторов маловато, чтобы можно было полноценно использовать json. В pg есть ещё целая куча функций для работы с ним.

json_each

json_each() позволяет преобразовать объекты в пары ключ-значение, к примеру:

select json_each(data) as pairs
from orders
where data ->> 'customer' like 'John%';

pairs

---

(customer,"""John Doe""")
(items,"{""product"": ""Beer"",""qty"": 3}")
(2 rows)

Мы выбираем объекты, где имя покупателя начинается с John, проходим по верхнеуровневым ключам и возвращаем пары ключ-значение.

Если нам нужны не объекты, а их строковое представление, есть парная функция json_each_text.

jsonobjectkeys

json_object_keys() позволяет вернуть набор верхнеуровневых ключей:

select distinct json_object_keys(data->'items') from orders;

## json_object_keys

qty
product
(2 rows)

В этом примере мы выбираем все присутствующие ключи из поля items и возвращаем только уникальные (distinct).

json_typeof

json_typeof() позволяет вернуть текстовое представление типа значения для заданного ключа. Он может быть number, boolean, null, object, array, или string.

select json_typeof(data -> 'items')
from orders;

## json_typeof

object
object
object
object
(4 rows)

Как видим, в items у нас везде хранятся объекты.

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

• JSON на физическом уровне хранится как текст, а JSONB хранится в виде особого, более эффективного, бинарного формата
• JSONB имеет больше функций для работы с ним.

Удач! 🎆