Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье я собираюсь объяснить, как вы можете получить значение свойства JSON с помощью оператора ->> PostgreSQL. Таким образом, мы можем преобразовать объект JSON, хранящийся в столбце json или jsonb , в таблицу виртуальной реляционной базы данных.
Хотя это хорошая идея-разработать схему базы данных в соответствии с реляционной моделью, бывают также ситуации, когда реляционная модель слишком строга.
Например, как я объяснил в этой статье , при реализации механизма журнала аудита с использованием триггеров базы данных очень удобно хранить старые и новые снимки строк в столбцах JSON, поскольку эта стратегия позволит в будущем изменять структуру целевой таблицы без необходимости изменять саму таблицу аудита. Более того, при использовании столбцов JSON мы можем даже использовать одну таблицу журнала аудита для хранения событий изменений, поступающих из нескольких таблиц.
Таблицы базы данных
Мы собираемся использовать те же таблицы базы данных, которые мы использовали при реализации таблицы журнала аудита с использованием триггеров PostgreSQL и столбцов JSONB:
В таблице книга хранятся все книги в нашей библиотеке, а в таблице book_audit_log хранятся события CDC (Запись данных об изменении) , которые генерируются всякий раз, когда книга запись изменяется с помощью инструкции INSERT, UPDATE или DELETE DML.
Предположим, что таблица book_audit_log содержит следующие данные:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-12-22 13:40:15 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-12-22 13:50:48 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-12-22 14:05:33 | Vlad Mihalcea |
Теперь мы хотим извлечь все версии моментальных снимков данной записи книги. Для этого мы можем использовать столбец new_row_data JSON, но вместо возврата результатов в формате JSON мы хотим воссоздать записи книги таблицы из объектов new_row_data JSON.
Используя оператор PostgreSQL ->> , мы можем получить значение свойства JSON и включить его в проекцию SQL-запроса, как показано в следующем SQL-запросе:
SELECT
dml_timestamp AS version_timestamp,
new_row_data ->> 'title' AS title,
new_row_data ->> 'author' AS author,
cast(
new_row_data ->> 'price_in_cents' AS int
) AS price_in_cents,
new_row_data ->> 'publisher' AS publisher
FROM
book_audit_log
WHERE
book_audit_log.book_id = :bookId
ORDER BY
dml_timestamp
Итак, при выполнении вышеуказанного SQL-запроса мы получаем точный набор результатов, который мы хотели:
| version_timestamp | title | author | price_in_cents | publisher | |---------------------|-----------------------------------------------|---------------|----------------|-----------| | 2020-12-22 13:40:15 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2020-12-22 13:50:48 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon | | 2020-12-22 14:05:33 | | | | |
Потрясающе, правда?
Вывод
Типы столбцов JSON очень полезны, когда нам нужно хранить неструктурированные данные, что относится к таблице журнала аудита, поскольку столбцы JSON работают намного лучше, чем модель строгих отношений.
И, благодаря оператору PostgreSQL ->> , мы можем получить значение свойства JSON из столбца JSON или JSONB и включить его в проекцию SQL-запроса. После этого мы можем практически использовать только что созданную виртуальную таблицу и воспользоваться всеми функциями SQL, предоставляемыми PostgreSQL, для дальнейшего преобразования данных в соответствии с нашими текущими бизнес-требованиями.