'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

enter image description here

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