CODEWITHSUNDEEP
sql-serversql-server-json
Ali
Ali

Reputation: 99

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

Upvotes: 0

Views: 86

Answers (1)

Thom A
Thom A

Reputation: 95564

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;

Upvotes: 1

Related Questions