Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье я собираюсь объяснить, как работает функция MySQL JSON_TABLE и как вы можете использовать ее для преобразования объекта JSON в таблицу реляционной базы данных.
При использовании системы реляционных баз данных лучше всего разрабатывать схему базы данных в соответствии с реляционной моделью. Однако существуют определенные случаи использования, когда реляционная модель слишком строга, и нам лучше хранить данные в виде столбцов типа JSON.
Например, как я объяснил в этой статье , при разработке таблицы журнала аудита гораздо удобнее хранить снимки строк до и после в столбцах JSON, поскольку эта стратегия позволит учесть будущие изменения целевой таблицы, а также избежать дублирования каждого столбца целевой таблицы в таблице журнала аудита. Благодаря подходу к столбцам JSON мы даже можем использовать единую таблицу журнала аудита для всех целевых таблиц, которые мы проверяем.
Таблицы базы данных
Мы собираемся повторно использовать те же таблицы базы данных, которые мы использовали при реализации таблицы журнала аудита с использованием триггеров MySQL и столбцов JSON:
В таблице книга хранятся все книги, которые есть в нашей библиотеке, а в таблице book_audit_log хранятся события CDC (Сбор данных об изменении) , которые создавались всякий раз, когда данная книга запись изменялась с помощью инструкции SQL INSERT, UPDATE или DELETE.
Предположим, что таблица 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-10-21 11:55:11 | 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-10-21 11:55:12 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-10-21 11:55:13 | Vlad Mihalcea |
Преобразование объекта JSON в реляционную таблицу с помощью функции MySQL JSON_TABLE
Теперь мы хотим извлечь все версии данной сущности книги. Для этого мы можем использовать столбец new_row_data JSON, но мы не хотим возвращать результаты в формате JSON. Вместо этого мы хотели бы воссоздать структуру книги таблицы, связанную с записью new_row_data .
К счастью, MySQL предоставляет нам функцию JSON_TABLE, которую мы можем использовать для преобразования объекта JSON в таблицу реляционной базы данных, как показано в следующем SQL-запросе:
SELECT
book_audit_log.dml_timestamp as version_timestamp,
r.*
FROM
book_audit_log,
JSON_TABLE(
new_row_data,
'$'
COLUMNS (
title VARCHAR(255) PATH '$.title',
author VARCHAR(255) PATH '$.author',
price_in_cents INT(11) PATH '$.price_in_cents',
publisher VARCHAR(255) PATH '$.publisher'
)
) AS r
WHERE
book_audit_log.book_id = :bookId
ORDER BY version_timestamp
При выполнении приведенного выше SQL-запроса мы получаем следующий набор результатов:
| version_timestamp | title | author | price_in_cents | publisher | |---------------------|-----------------------------------------------|---------------|----------------|-----------| | 2020-10-21 11:55:11 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2020-10-21 11:55:12 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon |
Функция JSON_TABLE определяет типы столбцов виртуальной таблицы и связанные с ними свойства объекта JSON, из которых будет извлечено значение столбца.
Хотя синтаксис MySQL для функции JSON_TABLE напоминает ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ , на самом деле он ведет себя как БОКОВОЕ СОЕДИНЕНИЕ , поскольку он соединяется как book_audit_log строки таблицы с базовым коррелированным подзапросом, который извлекает записи виртуальной таблицы, созданные путем вызова функции JSON_TABLE.
Как обрабатывать нулевые значения JSON при использовании функции MySQL JSON_TABLE
Однако результат не тот, что мы хотели, так как мы не получили строку, связанную с удалением записи книги.
Чтобы включить book_audit_log записи, имеющие NULL значение столбца new_row_data , мы можем использовать СОЕДИНЕНИЕ СЛЕВА между book_audit_log и виртуальной таблицей, созданной функцией JSON_TABLE, как показано в следующем SQL-запросе:
SELECT
book_audit_log.dml_timestamp as version_timestamp,
r.*
FROM
book_audit_log
LEFT JOIN
JSON_TABLE(
new_row_data,
'$'
COLUMNS (
title VARCHAR(255) PATH '$.title',
author VARCHAR(255) PATH '$.author',
price_in_cents INT(11) PATH '$.price_in_cents',
publisher VARCHAR(255) PATH '$.publisher'
)
) AS r ON true
WHERE
book_audit_log.book_id = :bookId
ORDER BY version_timestamp
Итак, при выполнении вышеупомянутого SQL-запроса мы теперь получаем ожидаемый набор результатов:
| version_timestamp | title | author | price_in_cents | publisher | |---------------------|-----------------------------------------------|---------------|----------------|-----------| | 2020-10-21 11:55:11 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2020-10-21 11:55:12 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon | | 2020-10-21 11:55:13 | | | | |
Потрясающе, правда?
Вывод
Типы столбцов JSON очень полезны, когда мы хотим хранить неструктурированные данные. Для нашей таблицы журнала аудита столбцы JSON работают намного лучше, чем при использовании строгой модели отношений для информации журнала аудита.
И, благодаря функции MySQL JSON_TABLE, мы всегда можем преобразовать объекты JSON в виртуальную таблицу отношений и воспользоваться всеми функциями SQL, предоставляемыми базовой базой данных, для дальнейшего преобразования данных.