Export SQL Table or a Query data to JSON string format | SQL Server 2016


As many of you know that in SQL Server 2016 there will be support for JSON data, and with the release of CTP2 you can play with the some of the features of JSON, but with CTP3/RTM JSON will be fully supported.

 

– With this CTP2 release you can only export data as JSON string.

– But with the release of CTP3 you will also be able to read JSON data by T-SQL query and convert it into tabular (row/column) format, and will support indexes.

 

–> JSON support is very similar to XML, so you can also:

1. Store JSON data in SQL Server in table columns as NVARCHAR datatype.

2. Export SQL tables rows to JSON data.

3. Query external JSON data and store back in SQL tables.

 

–> Just like XML for exporting JSON data you can use FOR JSON [AUTO | PATH] syntax:

1. FOR JSON AUTO: option automatically creates a nested JSON data with sub arrays based on the table hierarchy used in the Query. The AUTO option must have a FROM clause.

2. FOR JSON PATH: option enables you to define the structure of output of JSON data using the column names with aliases by using a dot separator.

 

–> Let’s see how export to JSON works:

– I’ll create a sample table and insert few rows in it:

 CREATE TABLE Students (
ID INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Class INT,
Marks DECIMAL(3,1)
)

INSERT INTO Students (FirstName, LastName, Class, Marks)
SELECT 'Manoj', 'Pandey', 10, 80.5
UNION ALL
SELECT 'Saurabh', 'Sharma', 11, 82.7
UNION ALL
SELECT 'Kanchan', 'Pandey', 10, 90.5

 

1. Let’s check the “FOR JSON AUTO” option:

 SELECT ID, FirstName, LastName, Class, Marks 
FROM Students
FOR JSON AUTO -- here

– Output with AUTO option:

[  
   {  
      "ID":1,
      "FirstName":"Manoj",
      "LastName":"Pandey",
      "Class":10,
      "Marks":80.5
   },
   {  
      "ID":2,
      "FirstName":"Saurabh",
      "LastName":"Sharma",
      "Class":11,
      "Marks":82.7
   },
   {  
      "ID":3,
      "FirstName":"Kanchan",
      "LastName":"Pandey",
      "Class":10,
      "Marks":90.5
   }
]

This option as mentioned previously formats the JSON document automatically based upon the columns provided in the Query.
 

2. Now let’s check the “FOR JSON PATH” option: with this option you can use the dot syntax as used in below Query to form a nested output.

SELECT
ID,
FirstName AS "StudentName.FirstName",
LastName AS "StudentName.LastName",
Marks
FROM Students
FOR JSON PATH -- here

– Output with PATH option:

[  
   {  
      "ID":1,
      "StudentName":{  
         "FirstName":"Manoj",
         "LastName":"Pandey"
      },
      "Marks":80.5
   },
   {  
      "ID":2,
      "StudentName":{  
         "FirstName":"Saurabh",
         "LastName":"Sharma"
      },
      "Marks":82.7
   },
   {  
      "ID":3,
      "StudentName":{  
         "FirstName":"Kanchan",
         "LastName":"Pandey"
      },
      "Marks":90.5
   }
]

As you can see with PATH option you can create wrapper objects (here “StudentName”) and nest properties (here “FirstName” & “LastName”).

 

--> FOR JSON PATH without FROM <table> clause:

 SELECT 
1 as 'a',
2 as 'b'
FOR JSON PATH

– Output without FROM clause:

{
"a": 1,
"b": 2
}

--> FOR JSON PATH without FROM clause and with ROOT option:

 SELECT 
1 as 'a',
2 as 'b'
FOR JSON PATH, ROOT('root')

– Output without ROOT option:

{
"root": {
"a": 1,
"b": 2
}
}

--> As I mentioned above "FOR JSON AUTO without FROM" clause is not supported

 SELECT 
1 as 'a',
2 as 'b'
FOR JSON AUTO

ERROR MESSAGE:

Msg 13600, Level 16, State 1, Line 116
FOR JSON AUTO requires at least one table for generating JSON objects.
Use FOR JSON PATH or add a FROM clause with a table name. 

 

–> You can also check the demo here:

 

Source: from my personal blog SQLwithManoj: http://sqlwithmanoj.com/2015/06/01/working-with-json-data-and-sql-queries-sql-server-2016/

Comments (1)
  1. 70-461 in process says:

    Thank you for article. I see they used 'FOR XML' LIKE AUTO/PATH syntax. Very nice for users who already get used to XML. They will do it too in quering part as I see it. It will be easy to switch to JSON when 2016 version be released.

    English is not my native.

Comments are closed.

Skip to main content