SQL Server JSON Functions

   

     JSON is very popular data format used in modern web applications. Microsoft decided to support JSON data type in SQL Server 2016. That decision opened a lot new ways for web developers to store and query data in SQL Server.
SQL Server has bunch of built-in functionality to work with JSON. I am going to focus on querying the JSON data in this article.

Before I go in details, lets talk about the data type. Unlike XML, JSON does not have its own data type name. SQL Server represents JSON objects as NVARCHAR type. Microsoft reasons for this decisions are:

• Migration : If you are already storing JSON as text, you don't need to worry about converting your data to a new data type.
• Cross Feature Compatibility : NVARCHAR is supported in all SQL Servers, so technically you can store JSON in any servers.
• Client-side Support : Since there is not a JSON data type, Developers do not need to worry about a new data type which might not be supported by the client software they use (.NET version, SSIS, SSRS, etc..)

There are 3 JSON functions you can use to read JSON values/objects in SQL Server 2016

ISJSON

As you can guess, it tests whether a string contains valid JSON

This will return all rows in Products table which have valid JSON in RowJson column.

---

JSON_VALUE

This function extracts value from JSON string. To be able to use this function. We need to look at JSON Path Expressions which is a way to write a query for JSON string.  A Path expression has two parts.

 1.) Optional path mode (lax or strict)           2.) The path itself

Path Mode

We need to declare the path mode at the beginning of the path expression. In the lax mode, the function returns empty values if the path expression contains errors. In strict mode, the functions raise errors if the path expression contains error. 

Path

After the path mode, we can start writing the path which is the query to find a value. Path must start with $ sign. It represents the context item. Path has the following options.

Key Names

These are the member names of your JSON.

$.Product.Name gives me the Name value of Product.

If the member contains special characters, you can surround it with quotes.

$."Product ".Name

Array Element

Arrays are zero based.

$.Product[2].Name gives me the second Products Name

The Dot Operator
It indicates a member of an object.

Now, we can use the JSON_VALUE function.

JSON_VALUE(column name, path expression)

This extracts the name of  Product in each row.

---

JSON_QUERY

This function returns an object or an array from JSON. It's syntax is exactly same with JSON_VALUE function.

JSON_QUERY(expression, [path])

This returns the Name JSON fragment from Products table.