SQL Server JSON – Part 2

Introduction

In the previous article, we covered the basics of JSON and showed how to use JSON in SQL Server, demonstrating these concepts with examples. In part two, we will cover more advanced topics of JSON, starting with specifics of handling JSON in SQL Server 2017 and later versions, continuing with JSON data validation, modifying JSON data, and concluding with best practices.

JSON in SQL Server 2017 and Later Versions

When XML was introduced in SQL Server 2005, it was implemented using XML data type. With the release of SQL Server 2016, the first version of JSON support came, but it was without native JSON data type. Microsoft stated a couple of reasons for their decision, including migration problems. Developers had already started using JSON with custom libraries before SQL Server 2016, and there was a lack of client-side support (most clients would still see it as string data-type).

Even if we accept these arguments, native JSON data types would bring significant benefits to performance and simplification of code. Since JSON data type is not supported in SQL Server versions 2017 and 2019, the community still hopes it will come out in the following releases. Until then, JSON data is stored in NVARCHAR columns as text. You should use the newly added COMPRESS function to compress stored textual data and convert it (internally) to a binary format.

JSON Data Validation

If you wish to validate a JSON string, you can use a built-in function ISJSON, which takes one string parameter, any type except TEXT or NTEXT, and returns 0 or 1, depending on the input parameter validity. The example is below:

SELECT
  ISJSON ('JSON_test'),
  ISJSON ('{}'), -- Correct
  ISJSON (''),
  ISJSON ('{JSON_test}'),
  ISJSON ('{"item"}'),
  ISJSON ('{"item":"10}'),
  ISJSON ('{"item":10}'); -- Correct

It is important to note that this function does not check whether the keys of the same level are unique, you have to check this in your code:

SELECT ISJSON ('{"item":"10", "item":"test"}') AS JSON_TEXT

As we are using text columns to store data types, one way to test the validity of JSON data is to create a table with a CHECK constraint that would use the ISJSON function to validate the input before data is saved to the table.

JSON Data Modification

You might want to update a part of the JSON data stored in the SQL Server column. SQL Server 2016 brings a JSON_MODIFY function that can be used to update the value of a single property, add an element to an array, insert a new property/value pair or delete property based on the given values. The function has three mandatory inputs – expression (column containing JSON text), path (JSON path expression), and new_value (new value for element specified in “path” parameter). The function returns updated JSON text. Using multiple examples, we demonstrate how to use this function.

Example 1. We update the JSON property value – update the product value in the existing JSON file. The first argument contains the original text, the second property path we are updating and the third is a new value:

SELECT JSON_MODIFY('{"Class":"","Method":"TEST_METHD"}', '$.Method', 'TEST_METHOD') AS 'JSON';

Example 2. We want to see values before and after the update, for logging or comparison reasons. We use the following query, declaring a variable and storing JSON text in it, while later we use JSON_MODIFY() function to get updated JSON:

DECLARE @OriginalJSON NVARCHAR(4000), @newvalue varchar(30),@path varchar(20)
Set @OriginalJSON='{"Class":"C#","Method":"TEST_METHD"}'
Set @newvalue='TEST_METHOD'
set @path='$.Method'
Select
        @OriginalJSON as 'Before Update',
        JSON_MODIFY(@OriginalJSON,@path, @newvalue) AS 'Updated JSON';

Example 3. We want to add a new property to the JSON string. To do this, we specify a new property and its value. JSON_MODIFY() function, in its default behavior, inserts this property since it doesn’t exist in the original JSON text:

DECLARE @OriginalJSON NVARCHAR(4000)
Set @OriginalJSON='{"Class":"C#","Method":"TEST_METHD"}'
Select
        @OriginalJSON as 'Before Update',
        JSON_MODIFY(@OriginalJSON,'$.ScreenSize',17) AS 'Updated JSON';

If we do not wish to use default behavior, we can use strict mode. In this case, JSON_MODIFY() will throw an exception if the property doesn’t exist in JSON text:

DECLARE @OriginalJSON NVARCHAR(4000)
Set @OriginalJSON='{"Class":"C#","Method":"TEST_METHD"}'
Select
        @OriginalJSON as 'Before Update',
        JSON_MODIFY(@OriginalJSON,'strict$.ScreenSize',17) AS 'Updated JSON';

Example 4. We want to remove the existing JSON property. To remove nodes or properties, we need to pass the NULL value as the third argument:

DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100);
SET @OriginalJSON = '{"Class":"C#","Class":"MTHD","Properties":["Inp","Out","Oth"]}';
SELECT @OriginalJSON AS 'Before Update', 
       JSON_MODIFY(@OriginalJSON, '$.Properties[0]', NULL) AS 'Updated JSON';

When we execute the above code, it replaces the array element with NULL. This is probably not what we wanted. One way to do it is to replace array values with new values, so we eliminate NULL values from the output.

DECLARE @OriginalJSON NVARCHAR(4000), @newjson VARCHAR(100);
SET @OriginalJSON = '{"Class":"C#","Class":"MTHD","Properties":["Inp","Out","Oth"]}';
set @newjson='["Inp2","Out2"]'
SELECT @OriginalJSON AS 'Before Update', 
       JSON_MODIFY(@OriginalJSON, '$.Properties', JSON_Query(@newjson)) AS 'Updated JSON';

Example 5. We want to rename a key. We do it in the same way as renaming existing columns in the database. We use nested JSON_MODIFY() functions combined with the JSON_VALUE function, creating a new key and dropping the existing key after copying its value to the new key.

DECLARE @OriginalJSON NVARCHAR(4000)
SET @OriginalJSON = '{"Class":"C#","Class":"Method"}';
SELECT @OriginalJSON AS 'Before Update', 
   JSON_MODIFY(
   JSON_MODIFY(@OriginalJSON, '$.OptionalClass',  JSON_VALUE(@OriginalJSON,'$.Class')),
                              '$.Class',NULL);

These examples show basic JSON data manipulation options. For more details, please refer to the official documentation.

Best Practices in Performance Optimization

Unlike XML, JSON is stored in text columns. While we can store XML as BLOB objects and use customized indexes on those columns, JSON data is stored as a simple UNICODE text and needs interpretation to be processed. It could impact performance in reading and writing large JSON documents. One option to mitigate this problem, on JSON columns that are smaller than 1700 bytes, is to create a non-clustered index on them or use them as included columns (in that case, the limit does not apply). Until SQL Server delivers a dedicated data format, performance improvements are limited. You could create computed columns and index them, or use full-text indexes, but with an increase in JSON document size, performance will degrade.

Let’s start with an example of the index on computed columns. First, we create a sample table and populate it with some data and then analyze performance using execution plans.

Initial table creation and data load (using AdventureWorks2019 database):

DROP TABLE IF EXISTS dbo.JSON_PERF;
CREATE TABLE dbo.JSON_PERF
(
   PK_ID NVARCHAR(1000) PRIMARY KEY,
   JSON_DATA NVARCHAR(4000) NOT NULL
);

INSERT INTO dbo.JSON_PERF( PK_ID, JSON_DATA )
SELECT LoginID,
       ( SELECT EInner.LoginID, EInner.HireDate 
		     FROM HumanResources.Employee EInner 
			WHERE EInner.LoginID = EOuter.LoginID FOR JSON AUTO
      )	   
  FROM HumanResources.Employee EOuter;
       
DROP TABLE IF EXISTS dbo.JSON_PERF;

Now we try a simple SELECT statement:

SELECT * FROM dbo.JSON_PERF 
                 WHERE JSON_VALUE(JSON_DATA, '$.HireDate') = '2009-02-08'

The above plan shows that a clustered index was performed since SQL Server was unable to lookup full strings in the JSON column efficiently. To improve this, we will use a computed column with the same expression and then use a dedicated non-clustered index on this column:

ALTER TABLE dbo.JSON_PERF 
                        ADD COMPUT_COL AS JSON_VALUE(JSON_DATA, '$.HireDate');
CREATE INDEX IDX_1 ON dbo.JSON_PERF(COMPUT_COL);

SELECT * FROM dbo.JSON_PERF 
              WHERE JSON_VALUE(JSON_DATA, '$.HireDate') = '2009-02-08';

This approach will work only for this attribute (in this case, “HireDate”), for others you need to create additional computed columns and indexes. An important thing to note with JSON indexes is that they are collation-aware, meaning that the result of the value function is a text value that inherits collation from the input variable. This also implies that values in the index will be sorted using collation rules as defined in source columns.

Conclusion

In this article, we continued with advanced JSON topics that show you, although support is not as strong as for XML, improvement with every new SQL Server version, So far, it is good enough to be used in most real-world applications. What is missing and should be added in the next versions, is JSON native data type. That would allow creating indexes on JSON columns for performance improvement. We showed how easy it is to integrate JSON text in SQL Server applications and how to execute most common operations, including parsing, validating, and modifying data. We concluded with the best practices for performance optimization but noted limitations of the current implementation. I hope you will enjoy using JSON in your next projects.