#arrays #json #sql-server #nested
#массивы #json #sql-сервер #вложенный
Вопрос:
Мне нужно извлечь информацию из столбца в формате JSON с помощью SQL Server. Проблема в том, что он вложен в разные объекты в массиве в объекте в массиве — и я как бы теряю след. В приведенном ниже примере кода я добираюсь только до извлечения массива actions — и тогда я застрял. К сожалению, я не слишком хорошо знаком с этим.
Я использую SQL Server 2017.
{
"actions":[
{
"class":"actions.entries.class",
"entries":[
{
"class":"actions.entry.class",
"id":null,
"key":"BirthDay",
"performance":{
"class":"actions.entry.performance.class",
"origin":null,
"originUuid":{
"class":"java.util.UUID",
"UUID":"3d6c5024-754f-477b-87bc-81d8e5ccadcd"
},
"performanceDateTime":1556012050827,
"performerUuid":{
"class":"java.util.UUID",
"UUID":"2647a005-a3a8-4362-8f2d-ddd188f500e7"
},
"registrationDateTime":null,
"userUuid":null
},
"type":"O",
"value":""2000-01-29T10:34:12.000Z""
},
{
"class":"actions.entry.class",
"id":null,
"key":"Gender",
"performance":{
"class":"actions.entry.performance.class",
"origin":null,
"originUuid":{
"class":"java.util.UUID",
"UUID":"3d6c5024-754f-477b-87bc-81d8e5ccadcd"
},
"performanceDateTime":1556012050827,
"performerUuid":{
"class":"java.util.UUID",
"UUID":"2647a005-a3a8-4362-8f2d-ddd188f500e7"
},
"registrationDateTime":null,
"userUuid":null
},
"type":"O",
"value":"Female"
}
]
}
]
}
Так, например, мне нужно найти значение для ключа «Пол», где значение для ключа «День рождения» не равно нулю: в данном случае «Женский». Для большей ясности я опустил другие объекты в массиве записей.
Любая помощь приветствуется!
Ответ №1:
Вы можете попытаться получить свои данные с помощью OPENJSON(). При таком подходе вы можете получить key/value пары из вашего вложенного JSON массива, даже если этот массив имеет разные имена ключей. Что вам нужно, так это ссылаться на объект JSON или массив с AS JSON предложением.
Ввод в формате JSON:
DECLARE @json nvarchar(max)
SET @json = N'{
"actions":[
{
"class":"actions.entries.class",
"entries":[
{
"class":"actions.entry.class",
"id":null,
"key":"BirthDay",
"performance":{
"class":"actions.entry.performance.class",
"origin":null,
"originUuid":{
"class":"java.util.UUID",
"UUID":"3d6c5024-754f-477b-87bc-81d8e5ccadcd"
},
"performanceDateTime":1556012050827,
"performerUuid":{
"class":"java.util.UUID",
"UUID":"2647a005-a3a8-4362-8f2d-ddd188f500e7"
},
"registrationDateTime":null,
"userUuid":null
},
"type":"O",
"value":""2000-01-29T10:34:12.000Z""
},
{
"class":"actions.entry.class",
"id":null,
"key":"Gender",
"performance":{
"class":"actions.entry.performance.class",
"origin":null,
"originUuid":{
"class":"java.util.UUID",
"UUID":"3d6c5024-754f-477b-87bc-81d8e5ccadcd"
},
"performanceDateTime":1556012050827,
"performerUuid":{
"class":"java.util.UUID",
"UUID":"2647a005-a3a8-4362-8f2d-ddd188f500e7"
},
"registrationDateTime":null,
"userUuid":null
},
"type":"O",
"value":"Female"
}
]
}
]
}'
Получить «Пол» и «День рождения»:
SELECT
t1.[value] AS Birthday,
t2.[value] AS Gender
FROM OPENJSON(@json, '$.actions')
WITH (
class nvarchar(max) '$.class',
entries nvarchar(max) '$.entries' AS JSON
) j
CROSS APPLY OPENJSON(j.entries)
WITH (
[key] nvarchar(100) '$.key',
[value] nvarchar(100) '$.value'
) t1
CROSS APPLY OPENJSON(j.entries)
WITH (
[key] nvarchar(100) '$.key',
[value] nvarchar(100) '$.value'
) t2
WHERE
t1.[key] = 'Birthday' AND
t2.[key] = 'Gender'
Вывод:
Birthday Gender
"2000-01-29T10:34:12.000Z" Female
Получить полные данные JSON:
SELECT
t1.class,
t2.id, t2.[key], t2.[type], t2.[value],
t3.class, t3.origin, t3.performanceDateTime, t3.registrationDateTime, t3.userUuid,
t4.class1, t4.UUID1,
t5.class2, t5.UUID2
FROM OPENJSON(@json, '$.actions')
WITH (
class nvarchar(max) '$.class',
entries nvarchar(max) '$.entries' AS JSON
) t1
CROSS APPLY OPENJSON(t1.entries)
WITH (
class nvarchar(1000) '$.class',
id nvarchar(100) '$.id',
[key] nvarchar(100) '$.key',
[type] nvarchar(100) '$.type',
[value] nvarchar(100) '$.value',
performance nvarchar(max) '$.performance' AS JSON
) t2
CROSS APPLY OPENJSON (t2.performance)
WITH (
class nvarchar(1000) '$.class',
origin nvarchar(100) '$.origin',
performanceDateTime nvarchar(100) '$.performanceDateTime',
registrationDateTime nvarchar(100) '$.registrationDateTime',
userUuid nvarchar(100) '$.userUuid',
originUuid nvarchar(max) '$.originUuid' AS JSON,
performerUuid nvarchar(max) '$.performerUuid' AS JSON
) t3
CROSS APPLY OPENJSON (t3.originUuid)
WITH (
class1 nvarchar(1000) '$.class',
UUID1 nvarchar(100) '$.UUID'
) t4
CROSS APPLY OPENJSON (t3.originUuid)
WITH (
class2 nvarchar(1000) '$.class',
UUID2 nvarchar(100) '$.UUID'
) t5
Вывод:
class id key type value class origin performanceDateTime registrationDateTime userUuid class1 UUID1 class2 UUID2
actions.entries.class BirthDay O "2000-01-29T10:34:12.000Z" actions.entry.performance.class 1556012050827 java.util.UUID 3d6c5024-754f-477b-87bc-81d8e5ccadcd java.util.UUID 3d6c5024-754f-477b-87bc-81d8e5ccadcd
actions.entries.class Gender O Female actions.entry.performance.class 1556012050827 java.util.UUID 3d6c5024-754f-477b-87bc-81d8e5ccadcd java.util.UUID 3d6c5024-754f-477b-87bc-81d8e5ccadcd
Обновить:
Если у вас есть данные JSON в виде значений в столбце таблицы, используйте этот подход для получения данных:
CREATE TABLE #Data (
JsonData nvarchar(max)
)
INSERT INTO #Data
(JsonData)
VALUES
(N'{"actions": "value1"}'),
(N'{"actions": "value2"}')
SELECT *
FROM #Data d
CROSS APPLY OPENJSON(d.JsonData) j
Вывод:
JsonData key value type
{"actions": "value1"} actions value1 1
{"actions": "value2"} actions value2 1
Комментарии:
1. Спасибо, Жоров! Однако я не могу поместить JSON в подобную переменную: он должен быть прочитан из столбца («DataColumn»). Мне нужно что-то вроде
DECLARE @json nvarchar(max) = JSON_QUERY(DataColumn, '$.actions') from DB.Tableно это, очевидно, не работает. Что я могу здесь сделать?2. @Cooz Use
CROSS APPLY, см. Обновленный ответ. Спасибо.3. Да, это именно то, что я искал. Большое вам спасибо.
Ответ №2:
Дайте мне знать, если это поможет вам.
DECLARE @json nvarchar(max) = N'{
"actions":[
{
"class":"actions.entries.class",
"entries":[
{
"class":"actions.entry.class",
"id":null,
"key":"BirthDay",
"performance":{
"class":"actions.entry.performance.class",
"origin":null,
"originUuid":{
"class":"java.util.UUID",
"UUID":"3d6c5024-754f-477b-87bc-81d8e5ccadcd"
},
"performanceDateTime":1556012050827,
"performerUuid":{
"class":"java.util.UUID",
"UUID":"2647a005-a3a8-4362-8f2d-ddd188f500e7"
},
"registrationDateTime":null,
"userUuid":null
},
"type":"O",
"value":""2000-01-29T10:34:12.000Z""
},
{
"class":"actions.entry.class",
"id":null,
"key":"Gender",
"performance":{
"class":"actions.entry.performance.class",
"origin":null,
"originUuid":{
"class":"java.util.UUID",
"UUID":"3d6c5024-754f-477b-87bc-81d8e5ccadcd"
},
"performanceDateTime":1556012050827,
"performerUuid":{
"class":"java.util.UUID",
"UUID":"2647a005-a3a8-4362-8f2d-ddd188f500e7"
},
"registrationDateTime":null,
"userUuid":null
},
"type":"O",
"value":"Female"
}
]
}
]
}';
SELECT
[PVT].[action_index]
,[CNV].[BirthDay]
,[PVT].[Gender]
FROM
(
SELECT
[action_index] = [actions].[key]
,[entry_field] = [entries].[key]
,[entry_value] = [entries].[value]
FROM OPENJSON(@json, '$.actions') AS [actions] -- iterate over actions array
OUTER APPLY OPENJSON([actions].[value], '$.entries') -- iterate over entries array for each action
WITH
(
[key] nvarchar(128)
,[value] nvarchar(max)
) AS [entries]
) AS [SRC]
PIVOT
(
MAX([entry_value]) FOR [entry_field] IN ([BirthDay], [Gender]) -- pivot the data only for the required fields
) AS [PVT]
CROSS APPLY
(
SELECT
[BirthDay] = TRY_CONVERT(datetimeoffset, NULLIF(REPLACE([BirthDay], '"', ''), ''))
) AS [CNV]
WHERE (1 = 1)
AND ([CNV].[BirthDay] IS NOT NULL);
Комментарии:
1. Спасибо, Гектор! Но поскольку предложение Жорова несколько более лаконично (без ‘PIVOT’, ‘TRY_CONVERT’ и так далее), я сначала попробую это. И — но это касается и ответа Жорова, поэтому я спрошу его тоже — я не могу поместить JSON в такую переменную: он должен быть прочитан из столбца («DataColumn»). Мне нужно что-то вроде
DECLARE @json nvarchar(max) = JSON_QUERY(DataColumn, '$.actions') from DB.Tableно это, очевидно, не работает. Что я могу здесь сделать? Еще раз спасибо!