Processing stored JSON data

More often than running queries to produce JSON, you'll be facing situations when you'll have data stored in the database as JSON and you'll need to query that data with JSON functions to link the values to other data stored in tables as regular columns. SQL Server has a set of functions that you can use to work with JSON data.

There are the five functions available to you:

• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• OPENJSON

The first function tests whether the input string is a valid JSON syntax for processing. SQL Server does not have a dedicated data type for JSON, so most of the time, the JSON record is stored as a NVARCHAR(max) data type, which allows you to store up to 2 GB of content to such a column. If we would like to test the validity of the string, we can query the output of the ISJSON function, as shown in the following example:

DECLARE @json NVARCHAR(4000) SET @json = '{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'

SELECT ISJSON(@json)

Such a SELECT statement should return a value of 1, if the input string is a valid JSON document. When we know that the string is valid, we can query the data from such a document using the OPENJSON function:

DECLARE @json NVARCHAR(4000) SET @json = '{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; 

SELECT * FROM OPENJSON(@json) 
WITH (
 name nvarchar(30), 
 surname nvarchar(30), 
 age int, 
 skills nvarchar(max) as json
)

The result of such a query would be a result set comprising four columns; the first three with the respective data type, and the fourth one a string with JSON representing the skills of the user.

For extracting individual values, you can use a built-in function named JSON_VALUE, which extracts scalar values of the specified path in the JSON document:

DECLARE @json NVARCHAR(4000) SET @json = '{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; 

SELECT 
 JSON_VALUE(@json,'$.name') name,
  JSON_VALUE(@json,'$.surname') surname,
  JSON_VALUE(@json,'$.age') age,
  JSON_QUERY(@json,'$.skills') skills

As you can see, the last function called was different. This is because the last field is not a scalar value, but a nested JSON object, so a different function has to be used here to produce the proper output. If you would like to parse that nested object and return, for example, the first skill available, you can use this with the JSON_VALUE function, and you'll need to navigate the array of skills and return the desired one. Considering that C# is the second skill in the skills array, we can return the skill with the following notation:

DECLARE @json NVARCHAR(4000) SET @json = '{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; 

SELECT 
 JSON_VALUE(@json,'$.name') name,
  JSON_VALUE(@json,'$.surname') surname,
  JSON_VALUE(@json,'$.age') age,
  JSON_VALUE(@json,'$.skills[1]') csharp

The last function JSON_MODIFY allows you to change the data in the JSON document, where you'll use same path navigation as with JSON_VALUE. To change the name of the user in the sample record from John to Mike, we'll use the following code:

DECLARE @json NVARCHAR(4000) SET @json = '{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; 

SELECT 
  JSON_MODIFY(@json,'$.name','Mike'),
  JSON_VALUE(@json,'$.name') name

This JSON_MODIFY function does not change the value stored in the variable; it just modifies the output of the SELECT statement with proper modification of the JSON document for further processing. If you evaluate the result of the following call to the JSON_VALUE function, it still returns the original name of the user.

Since JSON is not available as a native data type, it does not have a native form of indexing, as the XML data type mentioned earlier. However, if you're working with large JSON documents, there's a way to optimize the operations with full-text indexes:

--create a full text catalog 
CREATE FULLTEXT CATALOG jsonFullText
GO

--create a full text index on the column storing json data
CREATE FULLTEXT INDEX ON Person.Person_json(PhoneNumber)
KEY INDEX PK_Person_json_PersonID
ON jsonFullText

Once the full-text catalog and index are created, you can use the full-text functions available in SQL Server to search for the JSON data for particular strings. You have the following two functions available in SQL Server for the full-text operations:

• FREETEXT
• CONTAINS

While working with large datasets based on JSON documents, this can be very handy regarding the expected performance of the SQL Server.