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 ( 
        BlockIDINT'$.Block.BlockID',
        BlockchainFileIDINT'$.Block.BlockchainFileID',
        BlockVersionINT'$.Block.BlockVersion',
        BlockHashVARBINARY(32)'$.Block.BlockHash',
PreviousBlockHashVARBINARY(32)'$.Block.PreviousBlockHash',
BlockTimestampDATETIME'$.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!