Рубрики
Без рубрики

SQL Server OPENJSON – Сопоставление JSON с реляционной таблицей

В этой статье объясняется, как можно использовать функцию SQL Server OPENJSON для преобразования объекта JSON в таблицу реляционной базы данных.

Автор оригинала: Vlad Mihalcea.

Вступление

В этой статье я собираюсь объяснить, как работает функция SQL Server OPENJSON и как она может помочь вам преобразовать объект JSON в таблицу реляционной базы данных.

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

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

Таблицы базы данных

Мы собираемся повторно использовать те же таблицы базы данных, которые мы использовали при реализации таблицы журнала аудита с использованием триггеров SQL Server и столбцов JSON:

В таблице Книга хранятся все книги, которые у нас есть в нашей библиотеке, а в журнале Аудита книг таблицы хранятся CDC (Запись данных об изменении) события, которые создавались всякий раз, когда данная Книга запись была изменена с помощью инструкции SQL INSERT, UPDATE или DELETE.

Предположим, что таблица Журнал аудита книги содержит следующие данные:

| BookId | OldRowData                                                                                                                         | NewRowData                                                                                                                         | DmlType | DmlTimestamp            | DmlCreatedBy  |
|--------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------|
| 1      |                                                                                                                                    | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT  | 2021-02-18 11:40:28.343 | Vlad Mihalcea |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | UPDATE  | 2021-02-18 11:43:22.803 | Vlad Mihalcea |
| 1      | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} |                                                                                                                                    | DELETE  | 2021-02-18 11:44:25.630 | Vlad Mihalcea |

Преобразование объекта JSON в реляционную таблицу с помощью функции SQL Server OPEN JSON

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

К счастью, SQL Server предоставляет нам функцию OPENJSON, которую мы можем использовать для преобразования объекта JSON в таблицу реляционной базы данных, как показано в следующем SQL-запросе:

SELECT
   BookAuditLog.DmlTimestamp as VersionTimestamp,
   r.*
FROM
   BookAuditLog
OUTER APPLY
   OPENJSON (
     JSON_QUERY(
        NewRowData,
        '$'
     )
   )
   WITH (
      title varchar(255) '$.Title',
      author varchar(255) '$.Author',
      price_in_cents bigint '$.PriceInCents',
      publisher varchar(255) '$.Publisher'
   ) AS r
WHERE
   BookAuditLog.BookId = 1
ORDER BY 
   VersionTimestamp

При выполнении приведенного выше SQL-запроса мы получаем следующий набор результатов:

| VersionTimestamp        | Title                                         | Author        | PriceInCents | Publisher |
|-------------------------|-----------------------------------------------|---------------|--------------|-----------|
| 2021-02-18 11:40:28.343 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990         | Amazon    |
| 2021-02-18 11:43:22.803 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499         | Amazon    |
| 2021-02-18 11:44:25.630 |                                               |               |              |           |

Функция OPENJSON определяет типы столбцов виртуальной таблицы и связанные с ними свойства объекта JSON, из которых будет извлечено значение столбца.

Функция OPENJSON напоминает ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ . Поэтому нам нужно предложение OUTER APPLY , которое работает так же , как БОКОВОЕ СОЕДИНЕНИЕ , соединяющее строки BookAuditLog таблицы с базовым коррелированным подзапросом, который извлекает записи виртуальной таблицы, созданные вызовом функции OPENJSON.

Потрясающе, правда?

Вывод

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

И, благодаря функции SQL Server OPENJSON, мы всегда можем преобразовать объекты JSON в таблицы виртуальных отношений и воспользоваться всеми функциями SQL, предоставляемыми SQL Server, для дальнейшего преобразования данных.