SQL with Manoj

In my [previous post] we saw JOSN added as a new feature in SQL Server 2016.
 

–> Just like XML now 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.
 

–> Note:

– 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.
 

–> 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:

– Output with AUTO and ROOT() option:

SELECT ID, FirstName, LastName, Class, Marks 
FROM Students
FOR JSON AUTO, ROOT('StudList') -- here

– This is how a formatted JSON looks like:

{
  "StudList": [
    {
      "ID": 1,
      "FirstName": "Manoj",
      "LastName": "Pandey",
      "Class": 10,
      "Marks": 80.5
    },
    {
      "ID": 2,
      "FirstName": "Gaurav",
      "LastName": "Pandey",
      "Class": 11,
      "Marks": 82.7
    },
    {
      "ID": 3,
      "FirstName": "Garvit",
      "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”).
 

–> You can also check this demo in this video:

With the current release of CTP2 here you saw how we can export a SQL Table rows to JSON data. As soon as CTP3 will release we will see how can we read data back from from JSON string and convert it to tabular (row/column) format.
 

–> Check my [next post] on how to Import/Read JSON string and convert it in rational-tabular format in form of rows/columns.
 

Check more about JSON support on [MSDN BoL].