sql-serversql-server-json

SQL Server FOR JSON PATH


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

Solution

  • 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;