Archive

Posts Tagged ‘JSON SQL’

Parsing nested JSON in customized SQL Tabular format – MSDN TSQL forum

June 2, 2017 Leave a comment

 
–> Question:

Basically I have a JSON file output that I want to put into a SQL table, but no matter what syntax I try it doesn’t seem to be working.

This is my sample JSON file contents:

{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}

What I want is a SQL table that has 2 columns, column 1 will be item names (Destination Address,Origin Address, Distance Text, Distance Value, Duration Text & Duration Value) and column 1 will be the actual data.

I can get OPENJSON to do what I want for destination address & origin address but when I try and using OPENJSON to read the nested items that exist in ‘rows – > elements’ I can’t seem to pick them up. Can anyone help with the right syntax?
 

–> Answer:

declare @str varchar(4000) = 
N'{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}'

SELECT 
	CONCAT_WS(' ',Destination_Addresses, Origin_Addresses,Distance_Text,
		Distance_Value,Duration_Text,Duration_Value) as Col1
	,@str as Col2
FROM OPENJSON (@str)
WITH (
    Destination_Addresses    VARCHAR(1000) '$.destination_addresses[0]',
    Origin_Addresses   VARCHAR(1000) '$.origin_addresses[0]',
    Distance_Text    VARCHAR(1000) '$.rows[0].elements[0].distance.text',
    Distance_Value   VARCHAR(1000) '$.rows[0].elements[0].distance.value',
    Duration_Text    VARCHAR(1000) '$.rows[0].elements[0].duration.text',
    Duration_Value   VARCHAR(1000) '$.rows[0].elements[0].duration.value'
) AS Orders
Categories: SQL Server 2016, SQL Server Questions Tags: ,

Store JSON data in a table, OPENJSON and JSON_Value functions | SQL Server 2016 – Part 4

November 2, 2015 9 comments

In my previous posts I talked about how to [export] a Table or Query data into JSON string format, and [read it back] from JSON string to Relational-table format, and with [nested elements].
 

Here, in this post I’ll show how we can store JSON data in a normal table column, just like you store XML data.

XML data is stored in a column of XML datatype which also check the validity of the XML data to be stored. But to store JSON data there is no new datatype introduced, JSON can be stored in an NVARCHAR datatype column just like a plain text, and to validate it you can add a CHECK constraint on it.

IsJSON() function: can be used as a CHECK constraint on the columns that contain JSON string which will validate if the JSON string is in proper format or not.
 

As we will need AdvantureWorks2014 Sample Database in our example below, we need to upgrade its Compatibility from SQL 2014 to SQL 2016, i.e. from level 120 to 130, like:

USE [master]
GO

ALTER DATABASE [AdventureWorks2014] SET COMPATIBILITY_LEVEL = 130
GO

You can download AdvantureWorks2014 sample Database from Microsoft [CodePlex site].
 

–> Ok, now let’s create a new Table with OrderDetailsJSON column for storing JSON string with a CHECK constraint on it:

USE [AdventureWorks2014]
GO

CREATE TABLE CustomerOrder (
	BusinessEntityID INT, 
	FirstName NVARCHAR(50), 
	MiddleName NVARCHAR(50), 
	LastName NVARCHAR(50), 
	EmailPromotion INT,

	OrderDetailsJSON NVARCHAR(MAX) -- normal column with NVARCHAR datatype
		CHECK ( IsJSON ( OrderDetailsJSON ) = 1 ) -- CHECK Constraint to validate JSON string
)

 

–> Let’s create a sample record-set with JSON data in OrderDetailsJSON column. We will use FOR JSON AUTO option to convert relational data to JSON string for our example, as shown below:

;WITH CTE_PersonContact AS (
	SELECT 
		BusinessEntityID, FirstName, MiddleName, LastName, EmailPromotion,
		OrderDetailsJSON = 
		(	SELECT SalesOrderID, OrderDate, SubTotal, TaxAmt, TotalDue
			FROM [AdventureWorks2014].[Sales].[SalesOrderHeader] S
			WHERE S.CustomerID = P.BusinessEntityID
			FOR JSON AUTO -- here
		) -- our JSON column
	FROM [Person].[Person] P
)
INSERT INTO CustomerOrder
SELECT 
	BusinessEntityID, FirstName, MiddleName, LastName, EmailPromotion, 
	OrderDetailsJSON
FROM CTE_PersonContact
WHERE OrderDetailsJSON IS NOT NULL

-- (9778 row(s) affected)

–> Check the above inserted records with the OrderDetailsJSON column containing data in JSON format:

SELECT * FROM CustomerOrder

-- (9778 row(s) affected)