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: https://sqlwithmanoj.com/2015/06/01/working-with-json-data-and-sql-queries-sql-server-2016/

Comments

• Anonymous
  June 24, 2015
  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.