Add a string column from JSON object array in T-SQL-CodePudding

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.

CodePudding user response：

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