JSON and Integration with SQL Server 2016

Getting to know JSON

JSON (JavaScript Object Notation) is a language-independent text format designed for data interchange.   Objects and Arrays form the basis of the language.  Objects are collections of name/value pairs; arrays are ordered lists of values.   JSON is ‘self-describing’, meaning the definition and values can easily be understood from the format of the data.
{“games”:[
    {“gameName”:”Mario Kart 8″, “platform”:”Wii U”},
    {“gameName”:”Uncharted”, “platform”:”Playstation 4″},
    {“gameName”:”The Last Guardian”, “platform”:”Playstation 4″}]
}’
 
This is a basic example of an object (games), containing a 3-record array.  Each record has two items in a name/value pair.
JSON text can also be just the array, as shown below:
{  “First”:”1″,
       “Second”:”2″,
       “Third”:”3″
    }’
 
The following JSON text, structured with the parent object colors, is also valid:
{“colors”:[
{“First”:”1″},
       {“Second”:”2″},
       {“Third”:”3″}]
    }’
 
 
SQL Server 2016 contains functionality to test the validity of JSON text using the ISJSON built-in function.
This code will return 1 (valid JSON):
DECLARE @IsThisValidJSON NVARCHAR(4000) = N’
{“colors”:[
    {“First”:”1″},
       {“Second”:”2″},
       {“Third”:”3″}]
    }’
Select ISJSON(@IsThisValidJSON)
Pic1
 
This code block contains invalid JSON text, and will return 0:
DECLARE @IsThisValidJSON NVARCHAR(4000) = N'{ This certainly does not look like JSON }’
Select ISJSON(@IsThisValidJSON) as ValidCheck
Pic2
 

SQL Server/JSON integration

The biggest disadvantage to the way SQL Server 2016 handles JSON data is the lack of a dedicated datatype.
When XML support was introduced in SQL Server 2005, the XML datatype was included in the release to minimize development efforts and maximize performance.
Community reaction to this implementation has been almost entirely negative, with most developers considering this to be only a partial solution.
Despite this significant caveat, there are a number of useful functions for working with JSON text; however, they will suffer from standard performance difficulties of string manipulation operations in SQL Server.
 

Manipulating JSON text

SQL Server 2016 provides the OPENJSON function to convert JSON text to a relational format.
In its simplest form, the output returns a key/value pair and the datatype of the presumed column:
 
DECLARE @JsonOBJECT NVARCHAR(4000) = N’
{“PlanetID”:2,
“PlanetName”:”Pluto”,
“IsCold”:true
}’
Select * from OPENJSON (@JsonOBJECT)
Pic3
Datatypes 2, 1, and 3 are INT, String, and Boolean respectively.
For easier insertion, the table schema can also be specified using the WITH clause as part of the OPENJSON function:
DECLARE @JsonOBJECT NVARCHAR(4000) = N’
{“PlanetID”:2,
“PlanetName”:”Pluto”,
“IsCold”:true
}’
Select * from  OPENJSON (@JsonOBJECT)
WITH ( PlanetID INT, PlanetName Varchar(32), IsCold BIT)
Pic4
More detailed examples scan be found on MSDN.
 
Conversely, data stored relationally can be converted to JSON using the FOR JSON clause.
CREATE TABLE Planet (PlanetID INT IDENTITY, PlanetName Varchar(32), IsCold BIT)
GO
INSERT Planet (PlanetName, IsCold)
VALUES (‘Pluto’, 1), (‘Mercury’, 0), (‘Venus’, 0)
GO
Select * from Planet
FOR JSON AUTO
 
[{“PlanetID”:1,”PlanetName”:”Pluto”,”IsCold”:true},
{“PlanetID”:2,”PlanetName”:”Mercury”,”IsCold”:false},
{“PlanetID”:3,”PlanetName”:”Venus”,”IsCold”:false}]
 
The Auto parameter formats the data as part of the output.
FOR JSON PATH can be used for more granular control over the results.
 

Parsing JSON text

JSON_QUERY and JSON_VALUE can be used in SQL Server 2016 to parse JSON text.  They both perform the same function, but JSON_VALUE returns a scalar value while JSON_QUERY returns an object or array.
 
DECLARE @ParseJSON NVARCHAR(4000) =
N'{
    “topLevel”:{ 
      “scalarValue”:1,
      “arrayExample”:{ 
        “attrib1″:”First”,
        “attrib2″:”Second”,
        “attrib3″:”Third”
      },
      “scalarValue2″:”StringVal” }
}’
 
SELECT JSON_QUERY(@ParseJSON, ‘$.topLevel.arrayExample’) AS myArray
SELECT JSON_VALUE(@ParseJSON, ‘$.topLevel.arrayExample’) AS myArray
 
SELECT JSON_QUERY(@ParseJSON, ‘$.topLevel.scalarValue2’) AS myScalar
SELECT JSON_VALUE(@ParseJSON, ‘$.topLevel.scalarValue2’) AS myScalar
Pic5
As seen above, JSON_VALUE will return a NULL when executed against an array, while JSON_QUERY will return NULL against scalar values.

Conclusion

While the lack of a JSON datatype is perplexing, SQL Server 2016 has enough functionality for basic storage and retrieval of JSON text.  Hopefully a future version will improve the implementation.

About Anexinet

Anexinet is a leading professional consulting and services company, providing a broad range of services and solutions around digital disruption, analytics (and big data), and hybrid and private cloud strategies. Anexinet brings insight into how technology will impact how business decisions will be made and how our clients interact with their customers in the future.
Jeffrey Keller, Senior Consultant, JKeller@anexinet.com

Share on

Facebook sharing Linkedin sharing button Twitter sharing button