JSON support in SQL Server 2016

JSON (JavaScript Object Notation) is an open-standard, lightweight file format for storing and transporting data. It is easy for humans to read and write. It is easy for machines to parse and generate. SQL Server 2016 provides the built-in support for importing, exporting, parsing, and querying JSON documents.

A few new functions were introduced with SQL 2016 in order to support JSON natively in SQL Server 2016. These functions are:

• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• OPENJSON
• FOR JSON

SQL Server don’t have any specific data type to accommodate JSON. So we need to use NVARCHAR for storing JSON data.

Now lets look at the various new functions introduced in SQL Server 2016 for JSON.

ISJSON

We can say ISJSON is the simplest of the functions for JSON support in SQL Server. It checks if the supplied NVARCHAR text input is in proper format according to JSON specification or not. It takes one string argument as the input, validate it and returns 1 if the input is a valid JSON or returns 0 if it doesn’t.

If the provided input argument is NULL then the return value will also be NULL. It does not return errors. 

Syntax

ISJSON (expression)

Lets consider below JSON message and assign it to an NVARCHAR variable.

{"Employee": 
     {"firstName":"John",
       "lastName":"Smith",
       "email":"jsmith@test.com"
}}

Declaring a NVARCHAR variable and assigning the JSON message to it.

DECLARE @varJSON AS NVARCHAR(1000) = N'{"Employee": {
             "firstName":"John",
             "lastName":"Smith",
             "email":"jsmith@test.com"
}}'

Now let us use ISJSON function to test if this variable have a valid JSON message or not.

SELECT ISJSON(@varJSON)

You can read more about ISJSON here ISJSON (Transact-SQL)

JSON_VALUE

JSON_VALUE will return a scalar value from a JSON string for a requested key. It parses JSON string and extracts scalar value from JSON string by specific path.

JSON_VALUE (expression , path) 

Expression is the name of a variable or a column that contains JSON text and Path is the JSON path that specifies the property to extract.

We can use some specific format for providing the path. For example

In order to explain JSON_VALUE function, lets consider below JSON string.

DECLARE @varJSON AS NVARCHAR(1000) = N'{"Employee": {
            "firstName":"John",
            "lastName":"Smith",
            "email":"jsmith@test.com"},
             "Addresses":[ 
              { "Address":"1025 Gables Dr", "City":"Atlanta", "State":"Georgia"}, 
              { "Address":"1 JFK Blvd", "City":"Newark", "State":"New Jersey"} 
]
}'

Now let us use JSON_VALUE function to fetch Employee’s First Name and first Address.

SELECT JSON_VALUE(@varJSON,'$.Employee.firstName') 
SELECT JSON_VALUE(@varJSON,'$.Employee.Addresses[0].Address') 

JSON_VALUE returns null if specified path is not found in the JSON object. If you want to throw an error if specified path is not found in JSON object, you can use ‘strict‘ keyword prior to the path.

You can read more about JSON_VALUE here JSON_VALUE (Transact-SQL)

JSON_QUERY

JSON_QUERY function is used to extracts an object or an array from a JSON string. JSON _QUERY may sound similar to JSON_VALUE but the main difference between these two functions is that JSON_QUERY will returns an object or an array, while JSON_VALUE returns a scalar.

Syntax

JSON_QUERY (JSON String, path)

In order to explain JSON_QUERY, we will use the same JSON string which we used in the previous example.

DECLARE @varJSON AS NVARCHAR(1000) = N'{"Employee": 
				{"firstName":"John",
                  "lastName":"Smith",
                  "email":"jsmith@test.com"
				},
                "Addresses":[  
								{ "Address":"1025 Gables Dr", "City":"Atlanta", "State":"Georgia"},  
								{ "Address":"1 JFK Blvd", "City":"Newark", "State":"New Jersey"}  
                            ]
				}'

SELECT JSON_QUERY(@varJSON,'$.Employee')
SELECT JSON_QUERY(@varJSON,'$.Addresses')
SELECT JSON_QUERY(@varJSON,'$.Addresses[0]') 

The first query will return details which is under Employee path.

{ "firstName":"John",                    
  "lastName":"Smith",                    
  "email":"jsmith@test.com"      
}

The second query will return details which is under Addresses path.

[{ "Address":"1025 Gables Dr", "City":"Atlanta", "State":"Georgia"},            
 { "Address":"1 JFK Blvd", "City":"Newark", "State":"New Jersey"}                                ]

The third query will return details related to the first element under Addresses path. Hence it will return only the first detail set.

{ "Address":"1025 Gables Dr", "City":"Atlanta", "State":"Georgia"}

You can read more about JSON_QUERY here JSON_QUERY (Transact-SQL)

JSON_MODIFY

JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string. If you look closely, JSON_QUERY  is very similar to the xml.modify() functionality available in SQL Server.

JSON_MODIFY function can be used to either insert, update, delete or append a value to the JSON string.

JSON_MODIFY (expression , path , newValue)  

JSON_MODIFY takes three parameters, first parameter is a JSON string, second parameter is the path on which value needs to be changed and the third parameter is the value that needs to be updatde. This function returns updated JSON string as properly formatted JSON text.

JSON_MODIFY – Update an existing value

Let us see how we can use JSON_MODIFY function to update an existing value in a JSON message. In order to  update the value of existing JSON message, we need to provide the exact path of the property and the new value.

For example, using the following query we can update value of FirstName field of JSON string.

SET @varJSON = JSON_MODIFY(@varJSON,'$.Employee.firstName', 'Aaron')

JSON_MODIFY – Inserting a value