Skip to content

OPENJSON: Получение данных и PATH - часть 1

Добавил Sergey Moiseenko on

Пересказ статьи Marty Catherall. OPENJSON Getting to the data, and the PATH – PART I


Недавно мы использовали FOR JSON PATH для преобразования табличных данных (взятых непосредственно из таблицы SQL) в документ JSON.
PATH, о котором мы говорим здесь, используется с OPENJSON для получения данных, которые содержатся в документе JSON.

Мы видели пример OPENJSON и читали данные с явной схемой.

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": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"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
);
GO

Оказывается, что в этом примере данные находятся на самом верхнем уровне документа.

{
"configuration_id": 101,
"Configuration name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}

и поэтому нам не требовалось явно устанавливать PATH - однако мы могли бы это сделать. Вот так.

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": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"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
);
GO

Нижеприведенный запрос явно определяет 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 name": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}
')
WITH
(
[configuration_id] INT '$.configuration_id'
,[Configuration name] NVARCHAR(35) '$."Configuration name"'
,[Value] NVARCHAR(200) '$.Value'
,[minimum] NVARCHAR(200) '$.minimum'
,[maximum] NVARCHAR(200) '$.maximum'
,[value_in_use] NVARCHAR(200) '$.value_in_use'
,[description] NVARCHAR(200) '$.description'
,[is_dynamic] BIT '$.is_dynamic'
,[is_advanced] BIT '$.is_advanced'
);
GO

Обратите внимание, что _Configuration name_ содержит пробел. Поэтому в PATH мы просто заключаем это в двойные кавычки ".." - аналогично использованию квадратных скобок в выражениях T-SQL.
Давайте рассмотрим слегка измененный документ JSON, и посмотрим, как бы мы могли изменить 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": "recovery interval (min)",
"Value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"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"'
);

Обратите внимание на отсутствие 'пути по умолчанию' в предложении OPENJSON (после документа на строке 27).

Я надеюсь, что это помогло вам в путешествии по использованию JSON в SQL Server. А я скоро продолжу.
Категории: T-SQL

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Нет комментариев.

Автор не разрешил комментировать эту запись

Добавить комментарий

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Form options

Добавленные комментарии должны будут пройти модерацию прежде, чем будут показаны.