SQL Server FOR JSON PATH

84 views Asked by At

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"
               }
           ]
}
1

There are 1 answers

0
Thom A On BEST ANSWER

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;