'Add a string column from JSON object array in T-SQL
I want to create a single string from specific values in a nested JSON array and add the resulting string as a column. I have code similar to this:
DECLARE @json NVARCHAR(MAX) = N'[
{
"id": 2,
"info": {
"name": "John",
"surname": "Smith",
"age": 25
}
},
{
"id": 5,
"info": {
"name": "Jane",
"surname": "Smith",
"skills": [
{
"name": "SQL",
"group": "SQLnerds"
},
{
"name": "C#",
"group": "C#nerds"
},
{
"name": "Azure",
"group": "Azurenerds"
}
]
},
"dob": "2005-11-04T12:00:00"
}
]';
SELECT id,
firstName,
lastName,
age,
dateOfBirth,
JSON_VALUE(skills, '$[0].name') as singleskill,
skills,
STRING_AGG(CONVERT(nvarchar(max), JSON_VALUE(skills, '$.name')), ',') as skillstring
FROM OPENJSON(@json)
WITH (
id INT '$.id',
firstName NVARCHAR(50) '$.info.name',
lastName NVARCHAR(50) '$.info.surname',
age INT '$.info.age',
dateOfBirth DATETIME2 '$.dob',
skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
GROUP BY id, firstName, lastName, age, dateOfBirth, skills
The result I want is 2 lines with a skills column containing the json and a skillstring column containing a aggregated string with skill names like 'SQL,C#,Azure' and NULL if no skills are present.
I'm pretty sure the STRING_AGG can do this for me, but I cannot figure out how to get the values out of the JSON string.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

