Add a string column from json object array in T-SQL

February 12, 2022

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.

>Solution :

You can try to use OUTER APPLY with another OPENJSON by skills column, then you will get the skill name from JSON array per id.

final you might use STRING_AGG function to get your expect result

SELECT v.id,
      v.firstName,
      v.lastName,
      v.age,
      v.dateOfBirth,
      JSON_VALUE(v.skills, '$[0].name') as singleskill,
      v.skills,
      STRING_AGG( v2.skill_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
) as v 
OUTER APPLY OPENJSON(skills) WITH (
   skill_name NVARCHAR(100) '$.name' 
) as v2
GROUP BY  v.id, v.firstName, v.lastName, v.age, v.dateOfBirth, v.skills

;

sqlfiddle