Working with JSON functions in SQL Server

JSON has become the de facto standard for data interchange and its ubiquity in modern software applications makes the support that SQL Server offers for working with JSON more relevant than ever.

Whether you’re developing APIs, dealing with semi-structured data, or storing configuration, the ability to handle JSON natively at the SQL level will help make the applications you write both easier to develop and more efficient. It’s also ideal to have these functions available when performing any kind of maintenance procedures in your database that require the manipulation of JSON data.

With this in mind, I wanted to write a post that covers some of the key JSON functions you’ll need to know when working directly with JSON in a SQL Server (or Azure SQL) database. I always find it helpful to have a quick reference available for topics like this when the need arises.

JSON SQL Server support

Support for JSON was added to SQL Server 2016 and was made available for Azure SQL around the same time.

Unlike the support for XML in SQL Server, a new data type was not introduced specifically for JSON. Rather, JSON data in SQL Server is stored in NVARCHAR columns, either with a specific limit or the maximum possible size indicated via the MAX keyword.

Having said that, a new native JSON data type is currently being introduced to SQL Server and is in preview at the time of writing (March 2025). This new data type stores JSON data in a native binary format and is optimised to be more efficient with reads, writes, and storage. However, until this feature is officially released and you are ready to upgrade to the latest SQL Server version, storing your JSON data in NVARCHAR columns is the way to go.

The JSON functionality in SQL Server consists primarily of a set of functions that make working with JSON data intuitive and performant, and these will be covered in the following sections.

JSON functions

Let’s take a look at some of the key functions you’ll need to know when working with JSON in SQL Server. We’ll start with some of the simplest and most frequently used functions, working our way up to the more advanced and most recently added ones.

Note that in all the examples shown in the following subsections, the function input is an NVARCHAR variable. In many real-world cases, the input will instead be a column containing the JSON text.

ISJSON

Before parsing data that you expect to be JSON, it’s a good idea to validate that the data is valid JSON.

You can use the ISJSON function to check if a string is a valid JSON object or array as shown below.

SELECT ISJSON('{"valid": true}') AS IsValid; -- Returns 1
SELECT ISJSON('Not JSON') AS IsValid;        -- Returns 0

The function will return 1 if the specified string is valid JSON, otherwise, it will return 0.

You can customise the behavior of ISJSON by specifying the optional json_type_constraint argument as follows.

SELECT ISJSON('true', VALUE) AS IsValid; -- Returns 1

In the above example, the json_type_constraint argument is set to VALUE and, as a result, the function returns 1 because although ‘true’ is not a valid JSON object or array, it is a valid JSON value.

The possible json_type_constraint parameter values are as follows.

• VALUE
• ARRAY
• OBJECT
• SCALAR

ISJSON is useful for validation logic and filtering out or identifying malformed records.

JSON_VALUE

The JSON_VALUE function can be used to extract scalar values from JSON text.

DECLARE @Json NVARCHAR(MAX) = '{"title": "Buy milk", "isCompleted": false}';
 
SELECT JSON_VALUE(@Json, '$.title') AS Title; -- Buy milk

In the above example, the value of the “title” property is selected from the JSON object. Notice the usage of the $. syntax for accessing the property value.

When dealing with JSON arrays, specific elements can be accessed via their index as follows.

DECLARE @Json NVARCHAR(MAX) = '[
    {"title": "Buy milk", "isCompleted": false},
    {"title": "Leave out the trash", "isCompleted": false},
    {"title": "Clean room", "isCompleted": false}
]';
 
SELECT JSON_VALUE(@Json, '$[1].title') AS Title; -- Leave out the trash

In the above example, the value of a property from the second JSON object element within the array is accessed.

You can use JSON_VALUE  any time you need to retrieve a single value for a specific property within a JSON document.

Note that the JSON_VALUE function has a return type of NVARCHAR(4000), so you should be careful when using JSON_VALUE if there is a possibility that the length of the value returned could be greater than this. In these cases, JSON_VALUE returns NULL by default instead of the actual value.

JSON_QUERY

The JSON_QUERY function differs from JSON_VALUE in that it is used to extract a JSON fragment rather than a value; either a JSON object or array.

DECLARE @Json NVARCHAR(MAX) = '{
    "title": "Buy milk",
    "isCompleted": false,
    "tags": [
	"shopping",
	"groceries"
    ]
}';
 
SELECT JSON_QUERY(@Json, '$.tags') AS Tags; -- [    "shopping",    "groceries"   ]

In the above example, JSON_QUERY is used to retrieve the “tags” array from the JSON document.

You should use JSON_QUERY whenever you need to preserve the JSON structure. You can then use another function such as JSON_MODIFY to update values within the object or array that has been retrieved via JSON_QUERY.

JSON_MODIFY

The JSON_MODIFY function can be used to update, delete, or create JSON properties.

Update

To update a JSON property value, you can use the JSON_MODIFY function as shown below.

DECLARE @Json NVARCHAR(MAX) = '{"title": "Buy milk", "isCompleted": false}';
 
SET @Json = JSON_MODIFY(@Json, '$.isCompleted', CAST(1 AS BIT));
 
SELECT @Json AS Todo; -- {"title": "Buy milk", "isCompleted": true}

In the above example, the “isCompleted” property is retrieved from the JSON and its value is updated to true by casting the value 1 as a BIT.

It’s quite common when updating a JSON document that you’ll need to set the value of multiple properties simultaneously. To accomplish this, you’ll need to chain multiple JSON_MODIFY function calls together, as per the following example.

DECLARE @Json NVARCHAR(MAX) = '{
    "title": "Buy milk",
    "isCompleted": false,
    "tags": [
	"shopping",
	"groceries"
    ]
}';
 
SET @Json = JSON_MODIFY(
                JSON_MODIFY(
                    JSON_MODIFY(@Json, '$.title', 'Purchase milk'),
                '$.isCompleted', CAST(1 AS BIT)),
            '$.tags[1]', 'provisions');
 
SELECT @Json; -- {   "title": "Purchase milk",   "isCompleted": true,   "tags": [    "shopping",    "provisions"   ]  }

In the above example, three different property values are being set at the same time, including an element value within an array.

Chaining multiple function calls together can make things more difficult to follow. However, I find that formatting the SQL code using the style of indentation shown in the example above can help a lot with readability.

Delete

Deleting a property can be accomplished as follows.

DECLARE @Json NVARCHAR(MAX) = '{"title": "Buy milk", "isCompleted": false}';
 
SET @Json = JSON_MODIFY(@Json, '$.isCompleted', NULL);
 
SELECT @Json; -- {"title": "Buy milk"}

The “isCompleted” property is deleted by setting its value to NULL. This technique can also be used to delete objects and arrays from the JSON.

Create

Creating a new property can be accomplished in much the same way as updating a property.

DECLARE @Json NVARCHAR(MAX) = '{"title": "Buy milk", "isCompleted": false}';
 
SET @Json = JSON_MODIFY(@Json, '$.category', 'Errands');
 
SELECT @Json AS Todo; -- {"title": "Buy milk", "isCompleted": false,"category":"Errands"}

In the above example, the “category” property is added along with its value, since the property did not already exist within the JSON structure.

You can use the same technique that was shown in the Update subsection to create multiple new properties simultaneously.

JSON_OBJECT

The JSON_OBJECT function is my favourite JSON function. While it is seemingly simple, it makes it so much easier to create a JSON object compared to chaining multiple JSON_MODIFY function calls together.

The following example demonstrates how to create a basic JSON document using JSON_OBJECT.

SELECT JSON_OBJECT(
    'title': 'Buy milk',
    'isCompleted': CAST(1 AS BIT),
    'category': NULL
) AS Todo; -- {"title":"Buy milk","isCompleted":true,"category":null}

I love how simple it is to construct a JSON document in SQL this way compared to JSON_MODIFY.

Note that support for JSON_OBJECT was added in SQL Server 2022, so unfortunately this feature will only be available if you’re on this version or newer, or if you’re using Azure SQL.

Notice that unlike JSON_MODIFY, if you specify a property with a value of NULL, the property will be included in the JSON by default. You can customise this behaviour via the optional json_null_clause argument, as shown below.

SELECT JSON_OBJECT(
    'title': 'Buy milk',
    'isCompleted': CAST(1 AS BIT),
    'category': NULL ABSENT ON NULL
) AS Todo; -- {"title":"Buy milk","isCompleted":true}

In the above example, specifying ABSENT ON NULL after the property value indicates that the property should be excluded from the JSON output if the property value is NULL.

It’s also possible to create nested objects or arrays within the JSON by simply calling the JSON_OBJECT or JSON_ARRAY function wherever needed, as shown below.

SELECT JSON_OBJECT(
    'title': 'Buy milk',
    'isCompleted': CAST(1 AS BIT),
    'metadata': JSON_OBJECT('createdOn': GETUTCDATE()),
    'tags': JSON_ARRAY(
	'shopping',
	'groceries'
    )
) AS Todo; -- {"title":"Buy milk","isCompleted":true,"metadata":{"createdOn":"2025-03-26T21:05:17.843"},"tags":["shopping","groceries"]}

It’s not hard to imagine how much the complexity of constructing a JSON string is reduced by using these functions together, making it much safer than trying to piece things together by combining strings of data manually.

Summary

In this article, I walked through how to work with some of the most useful JSON functions that are available in SQL Server and Azure SQL databases.

After initially discussing version support, I started covering basic functions for validating JSON and retrieving JSON values.

I then moved on to additional functions that can be used for querying, creating, updating, and deleting JSON data. Lastly, I showcased the capabilities of newer functions that are available for constructing JSON objects in the latest versions of SQL Server and Azure SQL.