JSON Support in Sql Server 2016 CTP2 – Quick Start

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s