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

Формирование табличных данных с помощью FOR JSON PATH

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

Например, рассмотрим следующий документ JSON:

{
  "configuration_id": 101,
  "Configuration name": "интервал восстановления (мин)",
  "Value": 0,
  "minimum": 0,
  "maximum": 32767,
  "value_in_use": 0,
  "description": "Максимальный интервал восстановления в минутах",
  "is_dynamic": true,
  "is_advanced": true
}

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

Использование OPENJSON для извлечения данных

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

Вот пример использования OPENJSON для извлечения данных из ранее упомянутого документа JSON:

SELECT
     [configuration_id]
    ,[Configuration name] 
    ,[Value]          = TRY_CONVERT(sql_variant  , [Value]) 
    ,[minimum]        = TRY_CONVERT(sql_variant  , [minimum]) 
    ,[maximum]        = TRY_CONVERT(sql_variant  , [maximum]) 
    ,[value_in_use]   = TRY_CONVERT(sql_variant  , [value_in_use]) 
    ,[description]    = TRY_CONVERT(sql_variant  , [description]) 
    ,[is_dynamic]
    ,[is_advanced]
FROM
    OPENJSON
(
'
  {
    "configuration_id": 101,
    "Configuration name": "интервал восстановления (мин)",
    "Value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Максимальный интервал восстановления в минутах",
    "is_dynamic": true,
    "is_advanced": true
  }
'
)
WITH
(
     [configuration_id]     INT 
    ,[Configuration name]   NVARCHAR(35)
    ,[Value]                NVARCHAR(200)
    ,[minimum]              NVARCHAR(200)
    ,[maximum]              NVARCHAR(200)
    ,[value_in_use]         NVARCHAR(200)
    ,[description]          NVARCHAR(200)
    ,[is_dynamic]           BIT
    ,[is_advanced]          BIT
);

В этом примере данные извлекаются без явного указания PATH. Однако, при необходимости вы можете определить PATH в WITH-клаузе.

Указание PATH в WITH-клаузе

Если данные, которые вы хотите извлечь, не находятся на верхнем уровне документа JSON, вы можете указать PATH в WITH-клаузе функции OPENJSON.

Например, рассмотрим следующий документ JSON:

{
  "configuration_id": 101,
  "Configuration_Property": {
    "Configuration name": "интервал восстановления (мин)",
    "Value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Максимальный интервал восстановления в минутах",
    "is_dynamic": true,
    "is_advanced": true
  }
}

В этом случае данные, которые мы хотим извлечь, находятся вложенными в объект “Configuration_Property”. Мы можем указать PATH в WITH-клаузе для извлечения нужных значений:

SELECT 
 [configuration_id]
    ,[Configuration name] 
    ,[Value]          = TRY_CONVERT(sql_variant  , [Value]) 
    ,[minimum]        = TRY_CONVERT(sql_variant  , [minimum]) 
    ,[maximum]        = TRY_CONVERT(sql_variant  , [maximum]) 
    ,[value_in_use]   = TRY_CONVERT(sql_variant  , [value_in_use]) 
    ,[description]    = TRY_CONVERT(sql_variant  , [description]) 
    ,[is_dynamic]
    ,[is_advanced]
FROM
OPENJSON(
'
{
  "configuration_id": 101,
  "Configuration_Property": {
    "Configuration name": "интервал восстановления (мин)",
    "Value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Максимальный интервал восстановления в минутах",
    "is_dynamic": true,
    "is_advanced": true
  }
}
')
WITH
(
     [configuration_id]     INT            '$."configuration_id"'
    ,[Configuration name]   NVARCHAR(35)   '$."Configuration_Property"."Configuration name"'
    ,[Value]                NVARCHAR(200)  '$."Configuration_Property"."Value"'
    ,[minimum]              NVARCHAR(200)  '$."Configuration_Property"."minimum"'
    ,[maximum]              NVARCHAR(200)  '$."Configuration_Property"."maximum"'
    ,[value_in_use]         NVARCHAR(200)  '$."Configuration_Property"."value_in_use"'
    ,[description]          NVARCHAR(200)  '$."Configuration_Property"."description"'
    ,[is_dynamic]           BIT            '$."Configuration_Property"."is_dynamic"'
    ,[is_advanced]          BIT            '$."Configuration_Property"."is_advanced"'
);

В этом примере мы указываем PATH в WITH-клаузе для извлечения нужных значений из вложенного объекта.

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

Надеюсь, эта статья помогла вам понять, как использовать OPENJSON в SQL Server. Следите за новыми советами и трюками по SQL Server. Хорошего дня!

С наилучшими пожеланиями,

Марти