SharePoint Pals
 | Sign In
SQL Server 2016 and JSON Data
by Krishna KV 25 Jun 2016
Author
Team Leader, Aspire Systems
Visits   
Today  :   2     Total  :    4114

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;
blog comments powered by Disqus

SharePoint Pals

Pals
SharePoint Pals, a community portal for SharePoint developers, Administrators and End Users. Let's join hands and share the point together.
Read this on mobile

Training

Angular Js Training In Chennai
Advanced Angular Js training with real world developer scenarios
Angular Js, Web Api and Ionic for .Net Developers
All in one client side application development for .Net developers
Angular Js For SharePoint Developers
Get ready for the future. Its no more just C#

Get Connected

SharePoint Resources

SharePoint 2013 and 2010 Web Parts
Free Web Parts with Source Code for SharePoint Community




SharePoint 2013 Books and Tutorials
Collection of free SharePoint 2013 books and tutorials (eBooks, pdfs)

Supported By

Contribute your article and be eligible for a one month Free Subscription for Plural Sight. The Author of the most popular New Article (published in the previous month) will be awarded with a Free One month Plural Sight Subscription. Article can be sent to articles@sharepointpals.com in a word document.

Related Resources

Recent Tweets

Twitter January 15, 00:25
How To Enable Target Value And Actual Value In #D3 Gauge Chart https://t.co/VxSi4QnNrC

Twitter January 15, 00:24
How To Open #SharePoint List Hyperlink Column In Modal #Popup Window https://t.co/EQ7HkoZDkX

Twitter January 15, 00:24
Quick Introduction To #Asp.NetCore And It’s Features https://t.co/zAXObHCFpH

Twitter January 15, 00:22
How To Configure #PerformancePoint Services To Use Secure Store In #SharePoint 2013 https://t.co/LEwnUoI7EY

Twitter January 15, 00:21
How To Block Or Disable #Office365 Services https://t.co/Yvp2VPFIRP

Follow us @SharePointPals
Note: For Customization and Configuration, CheckOutRecent Tweets Documentation