For last week, the SQL Server 2016 Community Technology Preview 3.0 has been available with a lot of enhancements on new features.
All info about CTP3 are on SQL Server Blog here.
I wrote some weeks ago, a blog about JSON Support in SQL Server 2016: SQL Server 2016: native support for JSON
With this new CTP version, JSON support has new enhancements and especially the OPENJSON function. When I wrote my blog, this option was not available.
Three other functions are available: JSON_VALUE, ISJSON and JSON_QUERY!
Now, it is time to test it! ➡
OPENJSON
The TVF (Table-Value Function) OPENJSON is the easy way to import the JSON format directly in a table.
The principle is simple as you can see on this picture.
How it works? I want to do exactly what is in the picture below.
As you can see, I use double quotes (“) for all data type in the JSON format.
In a second time, I delete all double quotes (“) from the Salary values.
The result is the same as previously.
I will do the date for the HireDate values.
As you can see in this latest test, it doesn’t work!
Finally, the best is to keep double quotes (“) in the JSON values.
In JSON structure, double quotes are just for string but in SQL Server you can use it for number without problems.
For more information, msdn link here.
JSON_VALUE
This function returns a single text with the value of your stored JSON text.
As example, I create a database JSON_TEST with a table JSON_EmployeesList and a jsonContent in nvarchar(MAX) to store the json text in the variable @JsonEmployeesArray.
I use JSON_VALUE function on the 4 objects in EmployeesArray
As you can see, we have the first line of the jsonContent.
Why? JSON_VALUE extracts only a scalar value and not an object or an array like JSON_QUERY
For more information, msdn link here.
ISJSON
It is a function used to test if your text is a JSON text and return 1 if it is true.
In the query plan, you can see that the filter is applied with my ISJSON function
For more information, msdn link here.
JSON_QUERY
This function is the twin of JSON_VALUE but like a twin, it is not really the same…
JSON_VALUE return a scalar value and JSON_QUERY returns an object or an array with the values of your stored JSON text.
As you can see in my example, the JSON_QUERY function returns an array for EmployeesArray and EmployeesArray.Name.
For EmployeesArray.Department, the function return NULL. Department in this example is not an array.
For more information, msdn link here.
To conclude, if you are very interested by these functionalities, you find on msdn a good comparison between these 3 functions here.
See you soon!