SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Understanding OPENJSON and FOR JSON in Azure and SQL 2016

By Ryan Loftin, (first published: 2016/05/05)

Prerequisites: A dev instance on Azure or SQL Server 2016 RC3

Some of us may be interested in consuming data from an API, .json file, or moving data from MongoDB or its Microsoft equivalent DocumentDB.  These are more complex JSON operations that I hope to include in a future article.  In order to understand the new JSON capabilities in SQL Server 2016, I have been playing around with the basic FOR JSON and OPENJSON commands.  These are very similar to the FOR XML sql commands.  All data is imported and exported as NVARCHAR datatype.

If you want a really good grasp of JSON schema and syntax, I highly reccomend Michael Droettboom's Understanding JSON Schema.  The tl;dr version is below:

Squiggles, Squares, Colons, Commas and $

  1. Squiggly brackets act as 'containers'
  2. Square brackets hold arrays
  3. Names and values are separated by a colon
  4. Array elements are separated by commas
  5. $ is a positional operator for the array

Let's first create the sample database, which will hold 6 rows of block data of the Bitcoin blockchain.

USE [master]
GO

CREATE DATABASE [Blockchain]
GO

ALTER DATABASE [Blockchain] SET COMPATIBILITY_LEVEL = 130
GO

USE [Blockchain]
GO

CREATE SCHEMA [btc]
GO

CREATE TABLE [btc].[Block](
	[BlockID] [bigint] NOT NULL,
	[BlockchainFileID] [int] NOT NULL,
	[BlockVersion] [int] NOT NULL,
	[BlockHash] [varbinary](32) NOT NULL,
	[PreviousBlockHash] [varbinary](32) NOT NULL,
	[BlockTimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_BlockID] PRIMARY KEY CLUSTERED 
(
	[BlockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [btc].[Block] ([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp]) VALUES (0, 0, 1, 0x000000000019D6689C085AE165831E934FF763AE46A2A6C172B3F1B60A8CE26F, 0x0000000000000000000000000000000000000000000000000000000000000000, CAST(N'2009-01-03T18:15:05.000' AS DateTime))
GO
INSERT [btc].[Block] ([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp]) VALUES (1, 0, 1, 0x00000000839A8E6886AB5951D76F411475428AFC90947EE320161BBF18EB6048, 0x000000000019D6689C085AE165831E934FF763AE46A2A6C172B3F1B60A8CE26F, CAST(N'2009-01-09T02:54:25.000' AS DateTime))
GO
INSERT [btc].[Block] ([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp]) VALUES (2, 0, 1, 0x000000006A625F06636B8BB6AC7B960A8D03705D1ACE08B1A19DA3FDCC99DDBD, 0x00000000839A8E6886AB5951D76F411475428AFC90947EE320161BBF18EB6048, CAST(N'2009-01-09T02:55:44.000' AS DateTime))
GO
INSERT [btc].[Block] ([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp]) VALUES (3, 0, 1, 0x0000000082B5015589A3FDF2D4BAFF403E6F0BE035A5D9742C1CAE6295464449, 0x000000006A625F06636B8BB6AC7B960A8D03705D1ACE08B1A19DA3FDCC99DDBD, CAST(N'2009-01-09T03:02:53.000' AS DateTime))
GO
INSERT [btc].[Block] ([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp]) VALUES (4, 0, 1, 0x000000004EBADB55EE9096C9A2F8880E09DA59C0D68B1C228DA88E48844A1485, 0x0000000082B5015589A3FDF2D4BAFF403E6F0BE035A5D9742C1CAE6295464449, CAST(N'2009-01-09T03:16:28.000' AS DateTime))
GO
INSERT [btc].[Block] ([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp]) VALUES (5, 0, 1, 0x000000009B7262315DBF071787AD3656097B892ABFFD1F95A1A022F896F533FC, 0x000000004EBADB55EE9096C9A2F8880E09DA59C0D68B1C228DA88E48844A1485, CAST(N'2009-01-09T03:23:48.000' AS DateTime))
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [master]
GO
ALTER DATABASE [Blockchain] SET  READ_WRITE 
GO

Now lets output the six rows of data in the Block table using the FOR JSON PATH command.  Note that FOR JSON AUTO will work in the example below, but the syntax will not create wrapper objects and nested properties if needed. 

SELECT 
	BlockID AS 		'Block.BlockID',
	BlockchainFileID AS 	'Block.BlockchainFileID',
	BlockVersion AS		'Block.BlockVersion',
	BlockHash AS 		'Block.BlockHash',
	PreviousBlockHash AS 	'Block.PreviousBlockHash',
	BlockTimestamp AS 	'Block.BlockTimestamp'
FROM btc.Block
FOR JSON PATH

When executed, the result will be one long string of NVARCHAR data.  To make sense of the data, I suggest a copy and paste of the output into NotePad++.  You can format the data by adding the JSToolNpp plugin available on SourceForge.  When added, execute from Plugins --> JSTool --> JSFormat.

The JSON output will start with an opening square bracket and a closing bracket ([]).  This is your array of data.  In order to consume this data back into the table with the OPENJSON command, you will simply need to add a schema wrapper around the JSON:

{
    "Block" :
    {
        "BlockArray" :

-- Insert FOR JSON PATH output here

    }
}'

Now let's TRUNCATE the table and insert the JSON data into a NVARCHAR variable.  From there we can consume the data back into the table:

TRUNCATE TABLE btc.Block;  -- Empty the table to reload through OPENJSON command

DECLARE @Block NVARCHAR(MAX);
SET @Block = N'
{
	"Block" :
	{
		"BlockArray" :
		[
			{
				"Block" :
				{
					"BlockID" : 0,
					"BlockchainFileID" : 0,
					"BlockVersion" : 1,
					"BlockHash" : "AAAAAAAZ1micCFrhZYMek0/3Y65GoqbBcrPxtgqM4m8=",
					"PreviousBlockHash" : "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
					"BlockTimestamp" : "2009-01-03T18:15:05"
				}
			},
			{
				"Block" :
				{
					"BlockID" : 1,
					"BlockchainFileID" : 0,
					"BlockVersion" : 1,
					"BlockHash" : "AAAAAIOajmiGq1lR129BFHVCivyQlH7jIBYbvxjrYEg=",
					"PreviousBlockHash" : "AAAAAAAZ1micCFrhZYMek0/3Y65GoqbBcrPxtgqM4m8=",
					"BlockTimestamp" : "2009-01-09T02:54:25"
				}
			},
			{
				"Block" :
				{
					"BlockID" : 2,
					"BlockchainFileID" : 0,
					"BlockVersion" : 1,
					"BlockHash" : "AAAAAGpiXwZja4u2rHuWCo0DcF0azgixoZ2j/cyZ3b0=",
					"PreviousBlockHash" : "AAAAAIOajmiGq1lR129BFHVCivyQlH7jIBYbvxjrYEg=",
					"BlockTimestamp" : "2009-01-09T02:55:44"
				}
			},
			{
				"Block" :
				{
					"BlockID" : 3,
					"BlockchainFileID" : 0,
					"BlockVersion" : 1,
					"BlockHash" : "AAAAAIK1AVWJo/3y1Lr/QD5vC+A1pdl0LByuYpVGREk=",
					"PreviousBlockHash" : "AAAAAGpiXwZja4u2rHuWCo0DcF0azgixoZ2j/cyZ3b0=",
					"BlockTimestamp" : "2009-01-09T03:02:53"
				}
			},
			{
				"Block" :
				{
					"BlockID" : 4,
					"BlockchainFileID" : 0,
					"BlockVersion" : 1,
					"BlockHash" : "AAAAAE6621XukJbJoviIDgnaWcDWixwijaiOSIRKFIU=",
					"PreviousBlockHash" : "AAAAAIK1AVWJo/3y1Lr/QD5vC+A1pdl0LByuYpVGREk=",
					"BlockTimestamp" : "2009-01-09T03:16:28"
				}
			},
			{
				"Block" :
				{
					"BlockID" : 5,
					"BlockchainFileID" : 0,
					"BlockVersion" : 1,
					"BlockHash" : "AAAAAJtyYjFdvwcXh602Vgl7iSq//R+VoaAi+Jb1M/w=",
					"PreviousBlockHash" : "AAAAAE6621XukJbJoviIDgnaWcDWixwijaiOSIRKFIU=",
					"BlockTimestamp" : "2009-01-09T03:23:48"
				}
			}
		]
	}
}'

INSERT INTO btc.Block([BlockID], [BlockchainFileID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp])
SELECT * 
FROM OPENJSON (@Block, '$.Block.BlockArray')
WITH ( 
        BlockID			INT		'$.Block.BlockID',
        BlockchainFileID	INT		'$.Block.BlockchainFileID',
        BlockVersion		INT		'$.Block.BlockVersion',
        BlockHash		VARBINARY(32)	'$.Block.BlockHash',
	PreviousBlockHash	VARBINARY(32)	'$.Block.PreviousBlockHash',
	BlockTimestamp		DATETIME	'$.Block.BlockTimestamp'	
	)  

You can see the OPENJSON command is using the NVARCHAR data inserted into the @Block variable.  From there it is establishing the positional operator ($) as Block.BlockArray and grabbing each row of data.  Microsoft has added a great new feature to SQL Server 2016!

 
Total article views: 2760 | Views in the last 30 days: 5
 
Related Articles
FORUM

blocking

blocking

FORUM

Blocking

Blocking & Blocked by

FORUM
FORUM

blocking

how long it is blocking

FORUM

Blocking Problem

Blocking Problem

Tags
for json auto    
for json path    
json    
openjson    
 
Contribute