SQL Server 2016 and JSON Data

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, '$');

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

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

SELECT JSON_QUERY(@json, ‘$’)

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');
 

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;

 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;

 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;

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;

 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;
 

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;