Tips and Tricks for Working with JSON Data in SQL Server

JSON (JavaScript Object Notation) has become a ubiquitous data format for storing and exchanging information. SQL Server 2016 and later versions provide robust support for working with JSON data. This article explores some useful tips and tricks for handling JSON in T-SQL.

Validating JSON

Before working with JSON data, it’s a good idea to ensure it’s valid. We can use the ISJSON() function to check:

DECLARE @json NVARCHAR(MAX) = N'{"name": "John", "age": 30}';
IF (ISJSON(@json) > 0)
    PRINT 'Valid JSON';
ELSE
    PRINT 'Invalid JSON';

Output:

Valid JSON

Extracting Values

Use JSON_VALUE() to extract scalar values:

DECLARE @json NVARCHAR(MAX) = N'{"name": "John", "age": 30}';
SELECT JSON_VALUE(@json, '$.name') AS Name,
       JSON_VALUE(@json, '$.age') AS Age;

Result:

Name  Age
----  ---
John  30

We can also use JSON_VALUE() to extract individual array elements, and we can use JSON_QUERY() to extract objects or arrays:

DECLARE @json NVARCHAR(MAX) = N'{"fruit": ["apple", "banana", "cherry"]}';
SELECT 
    JSON_QUERY(@json, '$.fruit') AS AllFruit,
    JSON_VALUE(@json, '$.fruit[1]') AS OneFruit;

Result:

AllFruit                       OneFruit
-----------------------------  --------
["apple", "banana", "cherry"]  banana

Querying JSON Data

The OPENJSON() function allows us to treat JSON as a table:

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "John"},
    {"id": 2, "name": "Jane"}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    id INT,
    name NVARCHAR(50)
);

Result:

id  name
--  ----
1   John
2   Jane

Modifying JSON

Use JSON_MODIFY() to update JSON values:

DECLARE @json NVARCHAR(MAX) = N'{"name": "John", "age": 30}';
SET @json = JSON_MODIFY(@json, '$.age', 31);
PRINT @json;

Result:

{"name": "John", "age": 31}

Creating JSON from Query Results

We can use FOR JSON PATH to convert query results to JSON:

SELECT TOP 3 ProductID, Name, ListPrice
FROM Product
FOR JSON PATH;

Example result:

[
    {
        "ProductID": 1,
        "Name": "Mountain Bike",
        "ListPrice": 1999.9900
    },
    {
        "ProductID": 2,
        "Name": "Road Bike",
        "ListPrice": 1499.9900
    },
    {
        "ProductID": 3,
        "Name": "Helmet",
        "ListPrice": 49.9900
    }
]

Indexing JSON

For better performance, consider creating indexes. We can’t create an index that directly references a JSON property, but we can create indexes on computed columns. Therefore, to index a JSON property, we can create a computed column that returns values from the JSON property that we want to use for filtering, then apply an index to the computed column.

Example:

ALTER TABLE Product
ADD ProductName AS CAST(JSON_VALUE(JsonData, '$.name') AS nvarchar(255));

CREATE INDEX IX_ProductName ON Product(ProductName);

This adds a computed column to the Product table. The computed column returns the value from the $.name property in the JSON document. We then create an index on that computed column.

Note that I cast the JSON property to nvarchar(255). If I hadn’t done this, it would’ve used nvarchar(4000) by default, which would return the following warning:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IX_ProductName2' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

Handling NULL Values

Be cautious with NULL values when working with JSON functions.

For example, the ISJSON() function behaves differently with NULL input compared to invalid JSON:

DECLARE @json1 NVARCHAR(MAX) = NULL;
DECLARE @json2 NVARCHAR(MAX) = N'Not a JSON string';

SELECT ISJSON(@json1) AS NullJSON,
       ISJSON(@json2) AS InvalidJSON;

Result:

NullJSON  InvalidJSON
--------  -----------
null      0

When the input is NULL, ISJSON() returns NULL, not 0. This means we can’t simply use ISJSON() to differentiate between NULL and invalid JSON.

Also, some JSON functions behave differently, depending on whether the path uses lax or strict mode. Lax mode will often return NULL in cases where strict mode would return an error.

Therefore, some best practices:

Always check if your JSON input is NULL before performing operations on it.
Use ISNULL() or COALESCE() to provide default values when working with potentially NULL JSON data.
Consider using strict mode to get more predictable behaviour.
When parsing JSON, explicitly handle NULL cases in your code to avoid unexpected results.

Here’s an example that checks various function calls for NULL:

DECLARE @json NVARCHAR(MAX) = NULL;

SELECT
    CASE
        WHEN @json IS NULL THEN 'Unknown'
        WHEN ISJSON(@json) = 0 THEN 'Invalid JSON'
        ELSE ISNULL(JSON_VALUE(@json, '$.name'), 'Unknown')
    END AS Name,
    CASE 
        WHEN @json IS NULL THEN 'JSON is NULL'
        WHEN ISJSON(@json) = 0 THEN 'Invalid JSON'
        WHEN JSON_VALUE(@json, '$.age') IS NULL THEN 'Age not provided'
        ELSE CAST(JSON_VALUE(@json, '$.age') AS VARCHAR(10))
    END AS Age;

Result:

Name     Age         
-------  ------------
Unknown  JSON is NULL

Using JSON in Stored Procedures

JSON can be used for passing multiple arguments to stored procedures:

CREATE PROCEDURE UpdateProduct
    @productData NVARCHAR(MAX)
AS
BEGIN
    UPDATE Product
    SET Name = JSON_VALUE(@productData, '$.name'),
        ListPrice = JSON_VALUE(@productData, '$.price')
    WHERE ProductID = JSON_VALUE(@productData, '$.id');
END;

-- Usage:
EXEC UpdateProduct N'{"id": 1, "name": "Mountain Bike", "price": 2300.50}';

This stored procedure accepts just one argument, but it’s entirely possible for that argument to contain JSON. In this case, the stored procedure uses JSON_VALUE() to extract each value it needs from the JSON document.