Анализ и преобразование данных JSON с помощью OPENJSON

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

Функция набора строк OPENJSON позволяет преобразовать текст JSON в набор строк и столбцов. После того как коллекция данных JSON будет преобразована в набор строк с помощью OPENJSON, вы сможете выполнять любые SQL-запросы к полученным данным или вставлять эти данные в таблицу SQL Server.

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

  • Из объекта JSON функция возвращает все пары "ключ-значение", которые она находит на первом уровне.
  • Из массива JSON она возвращает все элементы массива вместе с их индексами.

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

Вариант 1. OPENJSON с выходными данными по умолчанию

При использовании функции OPENJSON без предоставления явной схемы для результатов (то есть без предложения WITH после OPENJSON) функция возвращает таблицу со следующими тремя столбцами:

  1. Имя свойства в поступающем на вход объекте (или индекс элемента в поступающем на вход массиве).
  2. Значение свойства или элемент массива.
  3. Тип (например, строка, число, логическое значение, массив или объект).

OPENJSON возвращает каждое свойство объекта JSON или каждый элемент массива в виде отдельной строки.

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

Пример:

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

Результаты:

key значение type
имя Джон 1
surname; Доу 1
возраст 45 2
навыки ["SQL","C#","MVC"] 4

Дополнительные сведения об OPENJSON со схемой по умолчанию

Дополнительные сведения и примеры см. в разделе "Использование OPENJSON" с схемой по умолчанию (SQL Server).

Сведения о синтаксисе и использовании см. в разделе OPENJSON (Transact-SQL).

Вариант 2. Выходные данные OPENJSON с явной структурой

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

Ниже представлен краткий пример использования OPENJSON с явно заданной схемой выходных данных в предложении WITH.

Пример:

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
       {  
         "Order": {  
           "Number":"SO43659",  
           "Date":"2011-05-31T00:00:00"  
         },  
         "AccountNumber":"AW29825",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":1  
         }  
       },  
       {  
         "Order": {  
           "Number":"SO43661",  
           "Date":"2011-06-01T00:00:00"  
         },  
         "AccountNumber":"AW73565",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":3  
         }  
      }  
 ]'  
   
SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              Number   varchar(200) '$.Order.Number' ,  
              Date     datetime     '$.Order.Date',  
              Customer varchar(200) '$.AccountNumber',  
              Quantity int          '$.Item.Quantity'  
 )

Результаты:

Число Дата Клиент Количество
SO43659 2011-05-31T00:00:00 AW29825 1
SO43661 2011-06-01T00:00:00 AW73565 3

Эта функция возвращает и форматирует элементы массива JSON.

  • Для каждого элемента в массиве JSON функция OPENJSON создает новую строку в выходной таблице. Два элемента в массиве JSON конвертируются в две строки в таблице результатов.

  • Для каждого столбца, указанного с помощью синтаксиса colName type json_path, OPENJSON преобразует значение, найденное в каждом элементе массива по указанному пути, в указанный тип. В этом примере значения для столбца Date берутся из каждого элемента в пути $.Order.Date и преобразуются в значения даты и времени.

Дополнительные сведения об OPENJSON с явной схемой

Дополнительные сведения и примеры см. в разделе "Использование OPENJSON" с явной схемой (SQL Server).

Сведения о синтаксисе и использовании см. в разделе OPENJSON (Transact-SQL).

OPENJSON необходим уровень совместимости 130.

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

Проверить уровень совместимости можно в представлении sys.databases или в свойствах базы данных.

Изменить уровень совместимости базы данных можно с помощью следующей команды:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

Дополнительные сведения о JSON в SQL Server и базе данных SQL Azure

Видео Майкрософт

Примечание.

Некоторые ссылки на видео в этом разделе могут не работать в данный момент. Корпорация Майкрософт переносит содержимое, которое ранее транслировалось канале Channel 9, на новую платформу. Мы будем обновлять ссылки по мере переноса видео на новую платформу.

Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видео.

См. также