SQL Server 2016: JSON, Part 1 – Data Extraction

JSON is one of the most widely used data interchange formats. It is used as a storing format in several NoSQL solutions, in particular, in Microsoft Azure DocumentDB. In my opinion, today JSON is yet more popular than XML. One of the reasons of its popularity is more simple form and better readability in comparison with XML. Naturally, there was a long-standing need in having an option to process data in this format within SQL Server. In SQL Server 2016, this option has been established.

First of all, it is worth mentioning that there is no separate type for storing JSON, unlike XML. That is why, JSON is stored in the standard variables, or in the varchar or nvarchar fields.

In order to extract data from JSON to SQL Server 2016, the following 3 functions have been introduced: ISJSON, JSON_VALUE, JSON_QUERY.

The first function allows checking whether a text contains correct JSON format.

ISJSON

The function returns 1 if it is JSON, 0 if it is not JSON, and null if null was passed to it.

In order to extract a certain value, we can use the JSON_VALUE function.

JSON_VALUE

Finally, in order to extract a certain fragment from JSON, we can use the JSON_QUERY function.

JSON_QUERY

Here, I would like to go into detail about the JSON path expressions that are used in the JSON_VALUE and JSON_QUERY functions. As you might notice, they are quite simple.

$ – JSON entity reference in text (reference to JSON entity in text)

$.property1 – reference to property1

$.array1[0] – reference to the first array element array1 (numeration begins from zero, as in JavaScript)

$.property1.property2.property3 – reference to property3 that is nested into property2 and property1. This is the way how objects are extracted on several nesting levels.

$.”property name 1″ – if a property name contains special characters like white spaces, dollar signs etc., its name should be enclosed within the quotation marks.

Also, there are 2 types of such expressions, lax and strict. lax is used by default and you don’t need to specify anything else, but still you can do it forcibly. For this, you need to specify lax at the beginning of the expression, for example,“lax$.property1” or “strict$.property1”. The difference between these two types is that if you specify non-existing or wrong paths for functions, you will get NULL with lax expression, and an error, if you specify strict. For example, if you specify an expression that returns nonscalar value for the JSON_VALUE function, the lax expression will rerun NULL, while strict will throw an error.

lax

In case of using the strict type, we will get an error:

Msg 13608, Level 16, State 5, Line 16
Property cannot be found on the specified JSON path.

I would also like to mention that all these functions can work with both, functions, and table columns.

Table Data

That’s all about extracting data from JSON. In the next article, I will consider generation of JSON from the pre-existing relational data.

Sergey Olontsev

Sergey Olontsev

Software Expert at Kaspersky Lab
Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.
Sergey Olontsev

Sergey Olontsev

Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.