Cooz
Reputation: 93
How can I query with SQL Server 2017 for nested values in JSON?
I have to extract information from a JSON formatted column with SQL Server. The problem is that it is nested in different objects in an array in an object in an array - and I kind of lose track. In the below code sample I only get as far as the extracting the actions array - and then I'm stuck. I'm not too familiar with this, unfortunately.
I use 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"
}
]
}
]
}
So for example I need to find the value to key "Gender" where the value to key "BirthDay" is not null: "Female" in this case. For the sake of clarity I left out other objects in the entries array.
Any help is welcome!
Upvotes: 1
Views: 10874
Answers (2)
Zhorov
Reputation: 29943
You may try to get your data using OPENJSON(). With this approach you can get key/value pairs from your nested JSON array, even if this array has different key names. What you need is to reference JSON object or array with AS JSON clause.
JSON input:
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"
}
]
}
]
}'
Get "Gender" and "Birthday":
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'
Output:
Birthday Gender
"2000-01-29T10:34:12.000Z" Female
Get full JSON data:
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
Output:
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
Update:
If you have JSON data as values in table column, use this approach to get data:
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
Output:
JsonData key value type
{"actions": "value1"} actions value1 1
{"actions": "value2"} actions value2 1
Upvotes: 3
Hector Montero
Reputation: 221
Let me know if this help you.
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);
Upvotes: 0
Related Questions
- Querying nested Json with SQL Server
- TSQL FOR JSON nested value
- How to parse nested JSON array using SQL Server
- SQL-Query to get nested JSON Array
- Extract items from JSON containing a nested array
- Extracting the values of a nested array in JSON using SQL Server
- MS SQL json query/where clause nested array items
- Nested JSON using ms sql server
- Select row where nested JSON array contains certain value
- Querying JSON nested array object in SQL