CODEWITHSUNDEEP
sqljsonsql-servert-sqlsql-server-2016
Maddy29
Maddy29

Reputation: 3

FOR JSON in SQL Server 2016

Is there any way i can return JSON data in given below format using FOR JSON in SQL Server 2016? 

 { 
     "draw": 1, 
     "recordsTotal": 57, 
     "recordsFiltered": 57, 
     "data": 
     [ 
         {
         "first_name": "Airi", 
         "last_name": "Satou", 
         "position": "Accountant",
         "office": "Tokyo", 
         "start_date": "28th Nov 08", 
         "salary": "$162,700" 
         } 
     ]
 }

Tried the following query. But not getting the desired output - 

SELECT
    1 AS draw
    ,100 AS recordsTotal
    ,10 AS recordsFiltered
,(SELECT 
    first_name
    ,last_name
    ,position
    ,office
    ,start_date
    ,salary
FROM Employees
FOR JSON PATH,ROOT('Employees'),INCLUDE_NULL_VALUES)

The query given below is giving the output properly but not able to append columns "draw", "recordsFiltered" and "totalRows" in to the JSON -

SELECT 
        first_name
        ,last_name
        ,position
        ,office
        ,start_date
        ,salary
    FROM Employees
    FOR JSON PATH,ROOT('Employees'),INCLUDE_NULL_VALUES

Upvotes: 0

Views: 195

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

this should be quite the same with sys.objects as dummy table source (Btw: Always try to create a working stand-alone example, either by providing a table with some data or by using some general system table everybody can use)

SELECT
     1 AS draw
    ,100 AS recordsTotal
    ,10 AS recordsFiltered
,(
    SELECT TOP 3
     o.object_id
    ,o.name
    FROM sys.objects o
    FOR JSON PATH,INCLUDE_NULL_VALUES
  ) AS [data]
FOR JSON PATH;

The inner SELECT will create an array of objects and call it "data". The outer SELECT will put all this together.

The result

[  
   {  
      "draw":1,
      "recordsTotal":100,
      "recordsFiltered":10,
      "data":[  
         {  
            "object_id":3,
            "name":"sysrscols"
         },
         {  
            "object_id":5,
            "name":"sysrowsets"
         },
         {  
            "object_id":6,
            "name":"sysclones"
         }
      ]
   }
]

Hint

You might want to read about WITHOUT_ARRAY_WRAPPER, to control the array brackets...

Upvotes: 1

Related Questions