SQL Server 2016 and JSON Data


Krishna KV
Team Leader, Aspire Systems
Published On :   25 Jun 2016
Visit Count
Today :  1    Total :   7102
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

Sharegate: Kick-Ass Tool
Think Your SharePoint & Office 365 Are Secure ? Find Out Now!


SQL Server 2016 is the latest version with a variety of new features and enhancements that will provide us the better performance, security, integrated reporting and analytics capabilities.

In this article we can have look into how the SQL Server 2016 helps us to import and export the JSON data into the relation storage.

JSON manipulation

 DECLARE @json NVARCHAR(4000);
 SET @json = N'
 [
 {"ProductID":514,"Name":"LL Mountain Seat Assembly","ProductNumber":"SA-M198","ListPrice":133.3400},
 {"ProductID":515,"Name":"ML Mountain Seat Assembly","ProductNumber":"SA-M237","ListPrice":147.1400},
 {"ProductID":516,"Name":"HL Mountain Seat Assembly","ProductNumber":"SA-M687","ListPrice":196.9200},
 {"ProductID":517,"Name":"LL Road Seat Assembly","ProductNumber":"SA-R127","ListPrice":133.3400},
 {"ProductID":518,"Name":"ML Road Seat Assembly","ProductNumber":"SA-R430","ListPrice":147.1400}]
 }';
 
 SELECT  Value FROM    OPENJSON(@json, '$');

clip_image002

 SELECT * FROM OPENJSON(@json,'$')  
  WITH (Name VARCHAR(200) '$.Name',  ProductNumber NVARCHAR(50) '$.ProductNumber', ListPrice DECIMAL(10,2) '$.ListPrice')

clip_image004

SELECT Value AS Products FROM OPENJSON(@json, '$') FOR JSON PATH;

clip_image006

SELECT JSON_QUERY(@json, '$')

clip_image008

Table export

For using the single table, the path and auto clause will be generated similar output.

 SELECT TOP 2  Name , ProductNumber , ListPrice FROM Production.Product FOR  JSON AUTO;
 
 SELECT TOP 2  Name , ProductNumber , ListPrice FROM Production.Product FOR  JSON PATH;
 
 SELECT TOP 2  Name , ProductNumber , ListPrice FROM Production.Product FOR  JSON AUTO , ROOT('Products');
 

clip_image010

Table Joins

The auto clause automatically formatted the JSON query based on the query structure of the first column of the table as parent and second table as its properties.

 SELECT TOP 2
         prod.Name AS productName ,
         model.Name AS ModelName
 FROM    Production.ProductModel model
         JOIN Production.Product prod ON model.ProductModelID = prod.ProductModelID
 FOR     JSON AUTO;

clip_image012

 SELECT TOP 2
         model.Name AS ModelName ,
         prod.Name AS productName
 FROM    Production.ProductModel model
         JOIN Production.Product prod ON model.ProductModelID = prod.ProductModelID
 FOR     JSON AUTO;

clip_image014

 SELECT TOP 2
         prod.Name AS productName ,
         model.Name AS ModelName
 FROM    Production.ProductModel model
         JOIN Production.Product prod ON model.ProductModelID = prod.ProductModelID
 FOR     JSON PATH;

clip_image016

We can use the dot- separated column names for the nested result.

 SELECT TOP 2
         prod.Name AS 'Product.productName' ,
         prod.ListPrice AS 'Product.Price' ,
         model.Name AS 'Model.ModelName'
 FROM    Production.ProductModel model
         JOIN Production.Product prod ON model.ProductModelID = prod.ProductModelID
 FOR     JSON PATH;

clip_image018

 SELECT TOP 2
         prod.Name AS 'Product.productName' ,
         prod.ListPrice AS 'Product.Price' ,
         model.Name AS 'Model.ModelName'
 FROM    Production.ProductModel model
         JOIN Production.Product prod ON model.ProductModelID = prod.ProductModelID
 FOR     JSON AUTO;
 

clip_image020

Import json

 CREATE TABLE TestProduct
     (
       Name VARCHAR(200) ,
       ProductNumber VARCHAR(200) ,
       ListPrice DECIMAL(10, 2)
     );
 GO
 
 DECLARE @json NVARCHAR(MAX);
 SET @json = N'
 [{"Name":"Adjustable Race","ProductNumber":"AR-5381","ListPrice":0.0000},{"Name":"Bearing Ball","ProductNumber":"BA-8327","ListPrice":0.0000}]';
 
 INSERT  INTO TestProduct
         SELECT  *
         FROM    OPENJSON(@json, '$')  
  WITH (Name varchar(200) '$.Name', 
  ProductNumber nvarchar(50) '$.ProductNumber', ListPrice Decimal(10,2) '$.ListPrice')
 
 	   
 SELECT  * FROM    TestProduct;
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

Migratiin Tools for SharePoint