Please note: CTP2 has been superseded. See this post for information about JSON in Sql Server CTP3.2
-
This post is a quick tour of JSON Support in Sql Server 2016. Three key points:
1) There won’t be a native JSON type, instead it will be represented as an nvarchar.
2) There is no BSON or JSONB support.
3) Some JSON features will not be available until CTP3.
At the bottom I’ve linked some articles with more detail. Before we start – if you run this:
SELECT @@VERSION
…you should see something like this:
Microsoft SQL Server 2016 (CTP2.0) - 13.0.200.172 (X64)
May 21 2015 10:53:07
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
The FOR JSON clause has similarities to FOR XML.
--simplest usage--
SELECT * FROM sys.schemas FOR JSON AUTO
--add columnar expression--
SELECT 1 As ColumnarExpression, * FROM sys.schemas FOR JSON AUTO
--use the "PATH" keyword to add structure to the JSON document.
--note the dot syntax - which must be wrapped in quotes or "[]"
SELECT CAST(GETDATE() as date) As "MetaData.QueryDate"
,CONVERT(varchar(12), GETDATE(), 108) As "MetaData.QueryTime"
,name
FROM sys.databases FOR JSON PATH
--add a root JSON element
SELECT name FROM sys.schemas FOR JSON AUTO, ROOT('root_element')
--demonstrate that nvarchar is used as storage--
DECLARE @Json nvarchar(max) = (SELECT * FROM sys.objects FOR JSON AUTO)
PRINT @Json
--try returning JSON from a variable (probably NOT very useful)--
--errors!
SELECT @@SERVERNAME as servername FOR JSON AUTO
--this works
SELECT @@SERVERNAME as servername FROM (SELECT 1 As X) Dummy FOR JSON AUTO
Links
Microsoft’s design rationale and also lists features arriving in CTP3:
http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx
More detail on the FOR JSON clause:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/06/07/for-json-clause-in-sql-server-2016-ctp2.aspx
blog.philipsymons.com 