Содержание

• Вступление (и некоторые общие сведения)

• Немного теории

• Используемые функции PostgreSQL

• Описание используемого примера данных

• Предварительные шаги

• Запросы

• Краткий итог

• «Задачи»

Вступление (и некоторые общие сведения)

Поддержка JSON появилась в PostgreSQL в версии 9.2, и, в следующих версиях, происходило развитие этого функционала (в частности был существенно увеличен набор функций для работы с колонками типа JSON\JSONB).

Документация PostgreSQL, помимо самого описания функции для работы с JSON\JSONB, предоставляет простые примеры, иллюстрирующие работу той или иной функции (имеются ввиду примеры с параметрами вида a\b\foo\bar\baz - наверное не очень показательно), так что цель этого материала — предложить более практический, более показательный пример (примеры) работы с JSON\JSONB- данными.
Этот материал предназначен в большей степени для тех, кто только знакомится с PostgreSQL и его возможностями, содержит описание различных возможностей выборки, обновления и удаления данных из колонок типа JSON\JSONB. Предполагается, что читатель знаком с языком запросов SQL, хотя бы минимально

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

Некоторые соображения о применимости JSON\JSONB (когда это оправдано)

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

Но если данные можно нормализовать - значит надо нормализовать. Размещение данных в колонках типа JSON\JSONB без какой-то существенной необходимости — это избыточное и ненужное усложнение, которое только затруднит работу («подальше положишь — поближе возьмешь» — эта поговорка неуместна в данном случае).

Забегая вперед - используемый пример, на котором производятся запросы, все-таки носит «искусственный» характер (получается, что противоречит только что сказанному о применимости JSON).

Да, конечно, данные в используемом примере можно легко нормализовать, но исключительно в «учебных» целях, чтобы сделать пример показательным эти данные определенным образом преобразованы и сохранены в колонке типа JSONB.

JSON или JSONB?

В чем разница? Если очень коротко: JSONB — это двоичный формат, а JSON — обычный текст.

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

Немного теории

Для обращения к отдельным полям данных типа JSON\JSONB служат операторы:

-> - возвращает поле объекта JSON\JSONB (может вернуть и объект JSON\JSONB, в общем случае);

->> - возвращает результат типа text.

В запросах, которые приводятся ниже, можно видеть разницу между этими операторами.

Индексы.

PostgreSQL может создать индекс колонки типа JSONB (так же, как создаются индексы по колонкам обычных типов данных, но эта возможность все-таки относится только к колонкам типа JSONB, но не JSON),

Для индексации полей типа JSONB служит индекс типа GIN. Подробнее об этом типе индекса см. документацию PostgreSQL

Создать индекс GIN:

CREATE INDEX idx_name ON таблица USING gin (колонка);

Создание индекса колонки info таблицы books (это из примера, который используется в этом материале, колонка типа JSONB, см. подробнее ниже):
create index idx_info on books using gin (info);

Просмотреть индексы таблицы:

SELECT * FROM pg_indexes WHERE tablename = 'table name';

Просмотреть индексы таблицы books:

SELECT * FROM pg_indexes WHERE tablename = 'books';

Используемые функции PostgreSQL

Ниже приводится краткое описание используемых в запросах функций PostgreSQL

json_to_recordset(json)\jsonb_to_recordset(jsonb)
«Разворачивает JSON-массив верхнего уровня с объектами в набор строк, имеющих составной тип, определённый в предложении AS» (из документации).Как это синтаксически записывается, будет видно в запросах.

json_to_record(json)\jsonb_to_record(jsonb)
Разворачивает JSON объект в строку.
Аргументом этой функции является уже не массив объектов, а отдельный объект (отличие от предыдущей функции)..

json_array_elements(json)\jsonb_array_elements(jsonb)
Извлекает значения из массива JSON, тип возвращаемого значения JSON/JSONB

json_array_elements_text()\json_array_elements_text()
Преобразует значения массива JSON в набор текстовых значений

json_array_length(json)/jsonb_array_length(jsonb)
Возвращает число элементов в массиве JSON

jsonb_set()\jsonb_set()
Добавляет или обновляет данные внутри JSON\JSONB структуры

jsonb_pretty()
Преобразует объект JSON\JSONB в более «читаемый» вид

И еще две функции, относящиеся к так называемым «оконным» функциям (используются в запросах, поэтому включены в это описание):

row_number()
Позволяет нумеровать строки результата запроса

over()
Определяет область в результате запроса (своего рода «окно») , над которой выполняется оконная функция; если не указывать эту область, то функция будет применена ко всему результату запроса.

В этом материале row_number()/over() используются для только нумерации строк (over() без указания области).

Более сложный практический пример использования over() (применение over() к части запроса) можно посмотреть здесь (Яндекс диск)

Функции в PostgreSQL для JSON\JSOB типов - документация PostgreSQL

«Оконные» функции - документация PostgreSQL

На что необходимо обратить внимание (при создании запросов, использующих JSON\JSONB функции)

При создании запросов необходимо обращать внимание на типы аргументов соответствующих функций (JSON или JSONB). Как пример - функции jsonb_array_elements() и json_array_elements() (буква «b» в имени одной и отсутствие буквы «b» в имени другой функции указывает на то, что первая требует аргумент типа JSONB, а вторая — аргумент типа JSON).

Несоответствие функции и типа данных, который использует функция вызовет ошибку.

В используемом примере колонка с данными, над которой выполняются запросы (info), имеет тип JSONB, поэтому далее везде используются функции именно для этого типа (с буквой «b» в имени функции)

Описание используемого примера данных

БД примера - это 2 таблицы (достаточно): таблица categories — категории (тематика) книг и таблица books — непосредственно информация о книгах.

Таблица categories:
колонка id (первичный ключ);
колонка title (наименование категории, текстовое поле).

Таблица books:
колонка id (первичный ключ);
колонка info - содержит информацию о книгах в виде json-структуры (тип колонки jsonb).

Наименование категорий:
1. Художественная литература
2. Наука
3. Спорт

Книги.
Данные отдельно взятой книги (то, что в колонке info):
1. наименование;
2. категория, к которой относится книга (id категории, category_id);
3. издательство;
4. количество томов;

Также, книга — это (возможно), и некоторые другие дополнительные данные:

5. несколько томов (информацию о которых хорошо представить в виде массива);
6. свое наименование каждого тома (помимо наименования самой книги) ;
7. разный год издания разных томов;
8. количество страниц отдельно взятых томов.

И еще:

9. возможно, книга написана коллективом авторов (т. е. авторов может быть несколько, их хорошо представить авторов в виде массива, как и тома);
10. конечно есть и другая информация (УДК, ISBN и другое), эти данные пропущены, чтобы избежать ненужного для изложения усложнения.

И еще одно упрощение - будем считать, что, если книга состоит из нескольких томов, то автор (или авторы) «общие» для всего издания (для всех томов), т. е. нет авторов для отдельных томов.

Книги (простое перечисление для начала):

Игорь Владимирович Савельев
Курс общей физики, изд. Наука
Количество томов-3
том 1: Механика. Молекулярная физика, страниц 432, год издания 1986
том 2: Электричество и магнетизм. Волны. Оптика, страниц 496, год издания 1988
том 3: Квантовая оптика. Атомная физика. Физика твердого тела. Физика атомного ядра и элементарных частиц, страниц 320, год издания 1987

Котов Александр Александрович
Как стать гроссмейстером
изд. Физкультура и спорт,
1985
страниц 240

Валентин Саввич Пикуль
Крейсера
изд. Современник
1990
страниц 511

Валентин Саввич Пикуль
Фаворит
1992
изд. Современник
томов-2
том 1: Его императрица, страниц 599, год издания 1992
том 2: Его Таврида, страниц 540, год издания 1992

Эро Хювянен
Йоко Сеппянен
Мир Лиспа: введение в язык Лисп и функциональное программирование
изд. Мир
томов-2
том 1: Введение в язык Лисп и функциональное программирование, страниц 447, год издания 1990
том 2: Методы и системы программирования, страниц 319, год издания 1992

Необходимо обратить внимание на следующее:
1. есть книги из нескольких томов («Курс общей физики» и «Мир Лиспа...»);
2. есть однотомник (А. А. Котов, «Как стать гроссмейстером»);
3. есть книга, где авторов двое («Мир Лиспа:...»);
4. В. С. Пикуль — две книги этого писателя.

Данные представлены в виде массива, элементы которого - книги одного автора т. е. всего 4 элемента:

Савельев (1 книга),
Котов (1 книга),
Пикуль (2 книги),
Хювянен\Сеппянен (1 книга).

Другими словами, данные размещены по принципу: 1 строка — 1 автор (некоторое усложнение, чтобы выполнять запросы было «интереснее») . Таким образом, данные каждой строки — это массив JSON-объектов-книг.

Книги в виде структуры, тот же список (данные колонки info таблицы books):

[
{
"title": "Курс общей физики",
"authors": [
"Игорь Владимирович Савельев"
],
"volumes": [
{
"year": 1986,
"pages": 432,
"title": "Механика. Молекулярная физика.",
"number": 1
},
{
"year": 1988,
"pages": 496,
"title": "Электричество и магнетизм. Волны. Оптика.",
"number": 2
},
{
"year": 1987,
"pages": 320,
"title": "Квантовая оптика. Атомная физика. Физика твердого тела. Физика атомного ядра и элементарных частиц.",
"number": 3
}
],
"publishing": "Наука",
"category_id": 2,
"count_volumes": 3
}
]
[
{
"title": "Мир Лиспа",
"authors": [
"Эро Хювянен",
"Йоко Сеппянен"
],
"volumes": [
{
"year": 1990,
"pages": 447,
"title": "Введение в язык Лисп и функциональное программирование",
"number": 1
},
{
"year": 1990,
"pages": 319,
"title": "Методы и системы программирования",
"number": 2
}
],
"publishing": "Мир",
"category_id": 2,
"count_volumes": 2
}
]
[
{
"title": "Крейсера",
"authors": [
"Валентин Саввич Пикуль"
],
"volumes": [
{
"year": 1990,
"pages": 511
}
],
"publishing": "Современник",
"category_id": 1,
"count_volumes": 1
},
{
"title": "Фаворит",
"authors": [
"Валентин Саввич Пикуль"
],
"volumes": [
{
"year": 1992,
"pages": 599,
"title": "Его императрица",
"number": 1
},
{
"year": 1992,
"pages": 540,
"title": "Его Таврида",
"number": 2
}
],
"publishing": "Современник",
"category_id": 1,
"count_volumes": 2
} ]
[
{
"title": "Как стать гроссмейстером",
"authors": [
"Котов Александр Александрович"
],
"volumes": [
{
"year": 1985,
"pages": 240
}
],
"publishing": "Физкультура и спорт",
"category_id": 3,
"count_volumes": 1
}
]

Если книга — однотомник, то год издания и количество страниц - в массиве томов, как данные единственного тома.

Краткий итог (таблица books):
- одна строка — книги одного автора;
- авторы одной книги — массив;
- тома — массив.

Готовый дамп БД для этого материала с приведенными выше данными можно взять здесь (Яндекс диск)

Предварительные шаги

Для подготовки этого материала использовался PostgreSQL v. 15.10 и консольный клиент psql (конечно можно использовать и другими инструментами, pgadmin, web-приложение phppgadmin …). Далее описывается работа с psql (от суперпользователя postgres - для учебного примера допустимо) и терминалом ОС Linux.

Прежде чем восстановить дамп с данными, необходимо создать БД (library).
Для этого из терминала необходимо выполнить:

psql -U postgres

Создание БД (по умолчанию БД создаются в схеме public):

CREATE DATABASE library
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
TEMPLATE = template0
LC_COLLATE = 'ru_RU.UTF-8'
LC_CTYPE = 'ru_RU.UTF-8'
CONNECTION LIMIT = -1;

Если создание прошло успешно, будет возвращено:

CREATE DATABASE

Для того, чтобы еще раз убедиться, что БД создана, в psql можно просмотреть список БД:

\l

Для восстановления дампа БД, непосредственно из терминала (не в консоли psql), необходимо выполнить:

psql -U postgres library < 'путь в дампу БД/dump-library'

Строго говоря, при создании БД необходимо дать и необходимые права на эту БД (подключение, выборка, обновление), но т. к. работа ведется от имени суперпользователя postgres (для учебного примера допустимо), то предоставление прав не требуется.

Подключение к БД (далее команды консоли psql):

\c library

Просмотреть перечень таблиц созданной БД library:

\dt

Получить информацию о таблице books (команда покажет, что тип колонки info именно jsonb, также можно видеть список индексов):

\d books

Запросы

Самое интересное. Для начала можно просто вывести данные всех книг:

SELECT * FROM books;

Или, в более удобочитаемом виде (попутно можно отметить, как работает jsonb_pretty(), в частности обратить внимание на отступы, по которым можно видеть вложенность одних объектов в другие):

SELECT id, jsonb_pretty(info) as info FROM books ORDER BY id;

Пример 1. Наверное стоит начать с чего-то относительно простого — получить наименования всех книг:

SELECT
id, title
FROM
books,
jsonb_to_recordset(info) as info_common(title text);

Т. к. колонка info таблицы books - это массив объектов JSON, то для извлечения данных необходима функция jsonb_to_recordset() - т.к. необходимо обрабатывать именно массив (содержимое каждой строки колонки info - массив).

Функция jsonb_to_recordset() выдает отдельные объекты (книги), в запросе эти объекты именованы как info_common, в скобках info_common — одно из полей этого объекта-книги (поле title — наименование книги; можно указать все поля объекта, которые извлекает jsonb_to_recordset(), но это необязательно — синтаксис не требует описания всех полей, в приведенном примере указано только наименование книги, которое и требуется в результате запроса).

Работу jsonb_to_recordset() можно наглядно "изобразить" примерно так:
1) начальное состояние стопка книг (книги лежат друг на друге, то что на обложках прочитать нельзя)
2) jsonb_to_recordset() отработала, теперь книги лежат уже рядом друг с другом, и можно прочитать обложки отдельных книг (получить их наименования).

Результат запроса:

id |          title            
---+--------------------------  
3 | Крейсера  
3 | Фаворит  
4 | Мир Лиспа  
1 | Курс общей физики  
2 | Как стать гроссмейстером
(5 строк)

Можно заметить повторение значения id=3. Объясняется это тем что строка таблицы books с id=3 в поле info содержит 2 книги («Крейсера» и «Фаворит»).

Пример 2. Получить наименования всех книг посредством jsonb_array_elements().

SELECT
id, info_data->>'title' as title
FROM
books,
jsonb_array_elements(info) as info_data;

Результат:

id |          title            
----+--------------------------  
3 | Крейсера  
3 | Фаворит  
4 | Мир Лиспа  
1 | Курс общей физики  
2 | Как стать гроссмейстером
(5 строк)

jsonb_array_elements() позволяет «добраться» до отдельного элемента info, а далее можно получить то, что находится в поле (title), используя оператор «->>», который возвращает текстовое значение (наименование книги).

Пример 3. Получить авторов отдельно взятого издания (авторы — массив).
Для это можно снова использовать функцию jsonb_array_elements()

SELECT
id, author
FROM
books,
jsonb_to_recordset(info) as info_common(authors jsonb),
jsonb_array_elements(authors) as author
WHERE id=4;

Как и в предыдущем запросе, необходимо «добраться» до отдельных элементов того, что находится в колонке info — используем jsonb_to_recordset(), а для извлечение авторов происходит посредством json_array_elements()

Результат:

id |     author       
---+-----------------  
4 | "Эро Хювянен"  
4 | "Йоко Сеппянен"
(2 строки)

Значения в колонке author - в кавычках, причина этого — тип возвращаемого значения jsonb_array_elemеnts() - jsonb.
Это можно увидеть и с помощью функции pg_typeof().

Запрос и результат:

SELECT
id, pg_typeof(author)
FROM
books,
jsonb_to_recordset(info) as info_common(authors jsonb),
jsonb_array_elements(authors) as author
WHERE id=4;

id | pg_typeof  
----+-----------  
4 | jsonb  
4 | jsonb
(2 строки)

Один из способов избавиться от кавычек в результате запроса - замена jsonb_array_elements() на jsonb_array_elements_text():

SELECT
id, author
FROM
books,
jsonb_to_recordset(info) as info_common(authors jsonb),
jsonb_array_elements(authors) as author
WHERE id=4;

Результат:

id|    author      
---+---------------  
4 | Эро Хювянен  
4 | Йоко Сеппянен
(2 строки)

Еще один способ аналогичен тому, что используется в примере 2 для получения наименования книги (info->>’title’), т. е. надо использовать что оператор ->>, который возвращает текстовое значение, только индекс в данном случае должен быть числовой (0 в данном случае).

SELECT
id, author->>0 as author
FROM
books,
jsonb_to_recordset(info) as info_common(authors jsonb),
jsonb_array_elements(authors) as author
WHERE id=4;

Пример 4. Пронумеровать авторов в результате запроса.

Для этого необходимо воспользоваться оконной функцией row_number(). Требуется использовать и функцию over().

Функция over() определяет к какой части результата будет применена нумерация. Если аргумент over() - "пустой" (ничего не указано), значит нумерация будет применена для всего результата запроса (что и требуется в данном случае).

SELECT
row_number() over() as author_number, author
FROM
books,
jsonb_to_recordset(info) as info_common(authors jsonb),
jsonb_array_elements_text(authors) as author
WHERE id=4;

Результат:

author_number |    author      
---------------------+---------------
             1 | Эро Хювянен
             2 | Йоко Сеппянен
(2 строки)

Пример 5. Посчитать количество страниц в каждом издании.

Чтобы посчитать количество страниц, необходимо:
1) получить каждый элемент колонки info при помощи jsonb_to_recordset() - т. е. извлечь данные каждой книги,
2) затем «добраться» до информации, которая описывает каждый том в отдельности (т. е. надо идти дальше «внутрь» структуры данных, получить volumes()), опять с помощью той же jsonb_to_recordset(), потому что volumes() - это массив,
3) и, уже непосредственно для подсчета количества страниц, применить агрегатную функция sum()

SELECT
id, title, sum (pages) as pages_book
FROM
books,
jsonb_to_recordset(info) as info_common(title text, volumes jsonb),
jsonb_to_recordset(volumes) as info_volumes(pages int) group by id, title;

Результат:

id |            title            | pages_book  
---+--------------------------------------+-----------------
 4 | Мир Лиспа                 |        766
 3 | Фаворит                   |       1139
 2 | Как стать гроссмейстером |        240
 3 | Крейсера                  |        511
 1 | Курс общей физики         |       1248
(5 строк)

Пример 6. Подсчет количества томов, через простое извлечение count_values

SELECT
id, title, count_volumes
FROM
books,
jsonb_to_recordset(info) as info_common(title text, count_volumes int);

Снова обращение к отдельных элементам поля info посредством jsonb_to_recordset(), и, затем, выбор count_volumes.

Результат:

id |           title           | count_volumes  
----+--------------------------------------+---------------  
3 | Крейсера                  |             1  
3 | Фаворит                  |             2  
4 | Мир Лиспа                 |             2  
1 | Курс общей физики         |             3  
2 | Как стать гроссмейстером |             1
(5 строк)

Пример 7. Подсчитать количество томов через jsonb_array_length().

В эту функцию (как аргумент) необходимо подставить массив volumes и тогда количество томов будет считать уже эта функция, а не функция sum().

SELECT
id, title, count_volumes_by_json_function
FROM
books,
jsonb_to_recordset(info) as info_common(title text, volumes jsonb),
jsonb_array_length(volumes) as count_volumes_by_json_function;

Результат:

id |           title              | count_volumes_by_json_function  
---+-------------------------------------+--------------------------------  
3 | Крейсера                  |                              1  
3 | Фаворит                   |                              2  
4 | Мир Лиспа                 |                              2  
1 | Курс общей физики         |                              3  
2 | Как стать гроссмейстером |                              1
(5 строк)

Пример 8. Получить наименования книг и общее количество страниц каждой книги.

Для получения такой информации необходимо извлечь данные каждой книги и данные каждого тома каждой книги, поэтому в запросе - 1-ый jsonb_to_recordset() извлекает данные по книгам, а 2-ой jsonb_to_recordset() извлекает данные по томам, количество страниц которых которые затем суммируется .

SELECT
id, title as title_book, sum(pages) as pages_in_all_volumes
FROM
books,
jsonb_to_recordset(info) as info_common(volumes jsonb, title varchar),
jsonb_to_recordset(volumes) as info_volumes(pages int)
GROUP BY id, title;

Результат запроса:

id |        title_book        | pages_in_all_volumes  
---+--------------------------+----------------------  
4 | Мир Лиспа                |                  766  
3 | Фаворит                  |                 1139  
2 | Как стать гроссмейстером |                  240  
3 | Крейсера                 |                  511  
1 | Курс общей физики        |                 1248
(5 строк)

Пример 8. Добавить к выборке наименование категории (для всех книг, вместе с id категории).

SELECT
books.id, info_common.title as title_book,
category_id, categories.title as title_category
FROM
books,
jsonb_to_recordset(info) as info_common(title text, category_id int)
LEFT JOIN categories
ON categories.id=category_id;

Результат:

id |         title_book           | category_id |       title_category                    
---+--------------------------------------+----------------+-----------------------------------  
3 | Крейсера                  |             1 | Художественная литература                            
3 | Фаворит                   |             1 | Художественная литература                            
4 | Мир Лиспа                  |             2 | Наука                                                
1 | Курс общей физики         |             2 | Наука                                                
2 | Как стать гроссмейстером |            3 | Спорт                                             
(5 строк)

Пример 9. Получить данные отдельной взятой книги, используя индекс книги в массиве объектов-книг.

Как уже говорилось, колонка info содержит массив книг. 1-я книга массива имеет индекс 0, 2-я (если она есть) — индекс 1 и т. д.

Данные и массива объектов можно извлекать, используя эти индексы (синтаксически — посредством оператора «->», info->0).

«Крейсера» - первая книга в массиве книг в строке с id=3, поэтому чтобы получить данные только этой книги в виде объекта JSON, требуется указать 0 в качестве индекса, так что info->0 в используемом примере из строки с id=3 возвращает данные «Крейсеров».

SELECT id, info->0 as book_0_info FROM books WHERE id=3;

Или, в более «читаемом» виде, используя jsonb_pretty():

SELECT id, jsonb_pretty(info->0) as book_0_info
FROM books
WHERE id=3;

Запрошенная информация выводится в виде единственного объекта, как колонка book_0_info:

id |           book_0_info             
---+----------------------------------
 3 | {                               +
   |     "title": "Крейсера",        +
   |     "authors": [                +
   |         "Валентин Саввич Пикуль"+
   |     ],                          +
   |     "volumes": [                +
   |         {                       +
   |             "year": 1990,       +
   |             "pages": 511        +
   |         }                       +
   |     ],                          +
   |     "publishing": "Современник",+
   |     "category_id": 1,           +
   |     "count_volumes": 1          +
   | }
(1 строка)

Можно поэкспериментировать с выдачей, выполняя запрос с указанием индекса (info->0) и запрос без указания индекса (во втором случае будут возвращаться обе книги из строки таблицы books.

SELECT id, jsonb_pretty(info->0) as book_0_info FROM books where id=3;

SELECT id, jsonb_pretty(info) as book_0_info FROM books where id=3;

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

Пример 10. Получить данные отдельной книги с помощью jsonb_to_record().

SELECT
id, title, publishing
FROM
books,
jsonb_to_record(info->0) as info_book(title text, publishing
text)
WHERE id=3;

В этом запросе функции jsonb_to_record() в качестве аргумента используется отдельно взятый объект (книга с индексом 0 из массива объектов-книг, info->0), из которого функция jsonb_to_record() извлекает отдельные поля, описанные как info_book.

id |  title   | publishing   
---+----------+-------------
 3 | Крейсера | Современник
(1 строка)

Это были запросы выборки данных. А если надо что-то обновить?

PostgreSQL может обновить отдельное значение структуры JSON (колонок типа JSON/JSONB)), посредством функции json_set().

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])
Здесь:
target jsonb — это целевое поле, поле, где предполагаются изменения (info);
path - «путь», по которому можно «дойти» до изменяемого поля в структуре JSON;
new_value — новое значение;
create_missing — false\true - необязательный параметр, как он работает:
- если create_missing = true и при этом поле, которое указано в path отсутствует, то это поле будет добавлено по указанному пути;
- если create_missing = false, то никакого добавления нового поля не произойдет (по сути ничего не произойдет, никаких изменений);
- если указанное в path поле существует, то оно будет обновлено (в любом случае, т. е. при любом значении create_missing)

Пример 11. Изменить наименование «Крейсера» на «Крейсера-крейсера»

Эта книга находится в строке с id=3, 1-я из двух книг соответствующего автора в этой строке, ее индекс равен 0 (это информация для понимания, как определить путь до наименования книги).

UPDATE books
SET info = jsonb_set(info, '{0, title}', '"Крейсера-крейсера"', false)
WHERE id=3;

Запрос обновляет таблицу books, строку с id=3, обновляется колонка info (1-ый аргумент json_set()).
'{0, title}' — path - тот самый «путь» к изменяемому значению.
Значение 0 — указывает обновить объект с индексом 0 в колонке info (т. е. «Крейсера»).
Значение title — это поле, которое меняем у объекта с индексом 0.
Последний аргумент функции jsonb_set() - false (ничего не добавляется, можно и не указывать как необязательный аргумент).

Чтобы увидеть результат, можно воспользоваться одним из тех запросов, которые были выполнены ранее, получение наименований всех книг.

SELECT
id, title
FROM
books,
jsonb_to_recordset(info) as info_common(title text);

Результат:

id|          title            
----+--------------------------  
4 | Мир Лиспа  
1 | Курс общей физики  
2 | Как стать гроссмейстером  
3 | Крейсера-крейсера  
3 | Фаворит
(5 строк)

Вернуть прежнее наименование:

UPDATE books
SET info = jsonb_set(info, '{0, title}', '"Крейсера"', false)
WHERE id=3;

Убедиться, что изменения произошли:

SELECT
id, title
FROM
books,
jsonb_to_recordset(info) as info_common(title text);

Пример 12. Поменять год издания 1-го тома романа «Фаворит» (пусть новое значение будет равно 1234).

Книга в строке с id=3, 2-я по счету, т. е. ее индекс в колонке info равен 1.

Тома (volumes), их индексы (аналогично массиву книг):
1-ый том — индекс 0,
2-ой том - индекс 1.

Т.к. надо менять год издания у 1-ого тома, то нужен элемент внутри volumes с индексом 0.

«Путь» (параметр path) в целом должен быть таким:
книга с индексом 1, том с индексом 0, поле, которое требуется изменить (year), в виде ожидаемого функцией кода это следует записать так - {1, volumes, 0, year}.
Последний аргумент функции jsonb_set() - false, как и в предыдущем случае.

UPDATE books
SET info = jsonb_set(info, '{1, volumes, 0, year}', '1324', false)
WHERE id=3;

Чтобы увидеть результат, можно воспользоваться одним из предыдущих запросов, ниже запрос и результат запроса:

SELECT
id, number, info_common.title as title_book,
info_volumes.title as title_volume, year
FROM
books,
jsonb_to_recordset(info) as info_common(title varchar, volumes jsonb),
jsonb_to_recordset(volumes) as info_volumes(number int, title varchar, year int)
WHERE id=3;

id | number | title_book |  title_volume   | year  
----+--------+------------+-----------------+------  
3 |        | Крейсера   |                 | 1990  
3 |      1 | Фаворит    | Его императрица | 1234  
3 |      2 | Фаворит    | Его Таврида     | 1992
(3 строки)

Значение 1234 появилось у 1-ого тома романа «Фаворит».

Вернуть назад (1234 сменить на предыдущее значение 1992)

UPDATE books
SET info = jsonb_set(info, '{1, volumes, 0, year}', '1992', false)
WHERE id=3;

Пример 13. Добавить данные к уже имеющимся данным (для какой-нибудь книги).

Книга «Крейсера» имеет на первой странице следующий подзаголовок - «Из жизни юного мичмана». Добавим этот подзаголовок (как поле subtitle), непосредственно к информации к самой книге (не к volumes), так как это можно видеть в самом издании.

Заодно (и для начала) убедимся, что если последний аргумент jsonb_set(), задан как false, то данные не добавятся (небольшой эксперимент):

UPDATE books
SET info = jsonb_set(info, '{0, subtitle}', '"Из жизни юного мичмана"', false)
WHERE id = 3;

Несмотря на то, что сервер ответил «UPDATE 1», изменений не произошло (чтобы увидеть это, можно воспользоваться уже знакомым запросом):

SELECT id, jsonb_pretty(info->0) as book_0_info
FROM books
WHERE id=3;

Запрос показывает что поле subtitle отсутствует. Тот же запрос обновления с последним параметром jsonb_set(), заданным как true:

UPDATE books
SET info = jsonb_set(info, '{0, subtitle}', '"Из жизни юного мичмана"', true)
WHERE id = 3;

Теперь запрос ниже покажет, что поле subtitle появилось среди прочих данных::

SELECT id, jsonb_pretty(info->0) as book_0_info
FROM books
WHERE id=3;

Результат:

id |                book_0_info                 
---+-------------------------------------------
 3 | {                                        +
   |     "title": "Крейсера",                 +
   |     "authors": [                         +
   |         "Валентин Саввич Пикуль"         +
   |     ],                                   +
   |     "volumes": [                         +
   |         {                                +
   |             "year": 1990,                +
   |             "pages": 511                 +
   |         }                                +
   |     ],                                   +
   |     "subtitle": "Из жизни юного мичмана",+
   |     "publishing": "Современник",         +
   |     "category_id": 1,                    +
   |     "count_volumes": 1                   +
   | }
(1 строка)

Кроме jsonb_set() для добавления данных можно также использовать (см. документацию PostgreSQL) функции json_insert()\jsonb_insert()

Пример 14. Удаление данных из структуры JSON.

Для начала (для лучшей наглядности) добавим подзаголовок и для второй книги той же строки таблицы books, в которой находятся данные «Крейсеров» (т. е. для «Фаворита»).

Никакого подзаголовка «Фаворита», как в «Крейсерах» нет, поэтому добавим (чтобы потом удалить) произвольный подзаголовок (необходимо обратить внимание на единицу в запросе ниже - '{1, subtitle}', «Фаворит» это 2-я книга массиве объектов-книг в строке с id=3, ее индекс соответственно равен 1).

UPDATE books
SET info = jsonb_set(info, '{1, subtitle}', '"Подзаголовок для удаления"', true)
WHERE id = 3;

Подзаголовок добавился:

SELECT
id, title, subtitle
FROM
books,
jsonb_to_record(info->1) as info_book(title text, subtitle text)
WHERE id=3;

Результат:

id |  title  |         subtitle           
---+---------+---------------------------
 3 | Фаворит | Подзаголовок для удаления
(1 строка)

Подзаголовок у «Крейсеров» никуда не «пропал» (запрос и результат):

SELECT
id, title, subtitle
FROM
books,
jsonb_to_record(info->0) as info_book(title text, subtitle text)
WHERE id=3;

id |  title   |        subtitle         
---+----------+------------------------
 3 | Крейсера | Из жизни юного мичмана
(1 строка)

Можно вывести и данных обеих книг строки с id=3 (разницу в запросах наверное легко можно видеть), запрос и результат:

SELECT
id, title, subtitle
FROM
books,
jsonb_to_recordset(info) as info_book(title text, subtitle text)
WHERE id=3;

id |  title   |         subtitle           
---+----------+---------------------------
 3 | Крейсера | Из жизни юного мичмана
 3 | Фаворит  | Подзаголовок для удаления
(2 строки)

Само удаление подзаголовка «Фаворита» (2-ая книга в строке с id=3, ее индекс в массиве книг равен 1).
Удаление производится аналогично вставке данных посредством функции jsonb_set(), синтаксически — «вычитание» из обновляемого объекта JSON данных по указанному пути.

UPDATE books
SET info = info #- '{1, subtitle}'
WHERE id=3;

Здесь символ «#» указывает серверу, что именно удалить (в прямом смысле подсказывает «путь»).

Убедиться, что удалилось корректно — повторить запрос выше:

SELECT  
id, title, subtitle  
FROM 
books,  
    jsonb_to_recordset(info) as info_book(title text, subtitle text)  
WHERE id=3;  

id |  title   |        subtitle         
----+----------+------------------------  
3 | Крейсера | Из жизни юного мичмана  
3 | Фаворит  |  
(2 строки)

Краткий итог

Каков итог. Можно говорить о том, что имеется полный набор возможностей для вставки.изменения.удаления данных колонок JSON\JSONB типов.
Вставка и удаление строк целиком - производится языковыми средствам.
Частичная выборка данные из колонки типа JSON\JSONB - оператор SELECT с использованием соответствующих функций для JSON\JSONB данных.
Обновить данные внутри колонки типа JSON\JSONB - оператор UPDATE и функции json_set()\jsob_set().
Удалить данные внутри JSON\JSONB структуры - как для обновления, оператор UPDATE и те же json_set()\jsob_set().

«Задачи»

Самостоятельно можно попробовать следующее:
1. Посчитать количество томов во всех книгах вообще.
2. Получить годы издания томов.
3. Удалить и заново добавить роман «Фаворит»
4. ...