So, I have a table with data like this
PersonID | Name | Address | Skills
---------+------+---------------+------------
1 | XYZ | Test Address | NA
2 | ABC | Test Address2 | Programming
I want to transform data into JSON with a SQL query like this
{
"PersonID":"1",
"Name": "XYZ",
"Address" : "Test Address",
"Skills": [
{
"Name":"NA"
}
]
}
Just nest FOR JSON
:
SELECT ID,
[Name],
Address,
(SELECT Skill AS Name
FOR JSON PATH) AS Skills
FROM (VALUES(1,'xyz','yyy','na'))V(ID,[Name],Address,Skill)
FOR JSON PATH;