Understanding OPENJSON and FOR JSON in Azure and SQL 2016

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.

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:

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!