JSON_CONTAINS Function in SQL Server 2025

Problem

I have data coming into my SQL Server database as JSON. Before I start parsing it which is quite intensive, I need to check if some values are present in the JSON. Is there a function I can use to do this? Let’s see what the new JSON_CONTAINS function in SQL Server 2025 can do.

Solution

The JSON file format has been supported in SQL Server since SQL Server 2016 with the introduction of functions like OPENJSON and JSON_VALUE. For an excellent introduction to the capabilities of SQL Server 2016 see these tips Advanced JSON Techniques in SQL Server Part 1, Part 2 and Part 3.

With each release, more functionality to handle JSON data has been added. Recently, the JSON data type (this tip has an good overview of which features were introduced in which version) was implemented in Azure SQL DB, which now has found its way into SQL Server 2025. This latest release of SQL Server is in preview at the time of writing. In contrast with many new SQL Server features, this preview includes new functionality that is only available in SQL Server 2025 and not in the cloud counterparts such as Azure SQL DB. One of those new features is the new function JSON_CONTAINS, the subject of this tip.

If you want to try out the SQL Server 2025 preview, you can either use a pre-configured VM image with SQL Server CTP 2.1 in Azure, or if you want the latest version of the preview (Release Candidate 0 a the time of writing) you can create an Azure VM and install SQL Server 2025 yourself. Keep in mind that functionality displayed in the preview might change when the final version release of SQL Server 2025.

The JSON_CONTAINS Function

The new JSON_CONTAINS function checks if a certain value occurs in a path of a JSON document. The syntax is as follows:

--MSSQLTips.com
JSON_CONTAINS(json document, searched value [, json path])

The first parameter is an expression in either the JSON data type, or in a character string value that contains JSON (such as NVARCHAR(MAX)).

JSON_CONTAINS Example

Let’s illustrate with an example. If you have the Adventure Works sample database installed (check out the tip Install AdventureWorks Database for SQL Server 2025), you can use the following script to create a table with the new JSON data type:

--MSSQLTips.com
DROP TABLE IF EXISTS dbo.JsonTest;
CREATE TABLE dbo.JsonTest
(
     Id             INT IDENTITY(1,1) PRIMARY KEY
    ,CustomerKey    INT
    ,Title          NVARCHAR(8)
    ,FirstName      NVARCHAR(50)
    ,MiddleName     NVARCHAR(50)
    ,LastName       NVARCHAR(50)
    ,Suffix         NVARCHAR(10)
    ,EmailAddress   NVARCHAR(50)
    ,Phone          NVARCHAR(20)
    ,JsonData       JSON
);
INSERT INTO dbo.JsonTest(
     CustomerKey 
    ,Title
    ,FirstName
    ,MiddleName
    ,LastName
    ,Suffix
    ,EmailAddress
    ,Phone
    ,JsonData)
SELECT
     CustomerKey
    ,Title
    ,FirstName
    ,MiddleName
    ,LastName
    ,Suffix
    ,EmailAddress
    ,Phone
    ,(
    SELECT
         CustomerKey    AS [CustomerKey]
        ,Title          AS [Name.Title]
        ,FirstName      AS [Name.FirstName]
        ,MiddleName     AS [Name.MiddleName]
        ,LastName       AS [Name.LastName]
        ,Suffix         AS [Name.Suffix]
        ,EmailAddress   AS [ContactInfo.EmailAddress]
        ,Phone          AS [ContactInfo.Phone]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) myjson
FROM AdventureWorksDW2022.dbo.DimCustomer;

We can now run the following query:

--MSSQLTips.com
SELECT FirstName, LastName
FROM dbo.JsonTest
WHERE JSON_CONTAINS(JsonData,'David','$.Name.FirstName') = 1;
json_contains example

Using Path

In this example, the path ‘$.Name.FirstName’ was used. If no path is specified, the default ‘$ ‘ is used which means the entire JSON document is searched. Interestingly enough, when the path parameter is omitted, the query doesn’t return any results.

path parameter is currently required. If nothing is specified, nothing is returned

This is most likely a limitation of the preview, as the documentation states that the path parameter is currently required, even though it’s listed as optional. This behavior might change with the official release of SQL Server 2025.

Using Wildcards

You can also specify wildcards in the JSON path parameter. For example:

--MSSQLTips.com
SELECT FirstName, LastName
FROM dbo.JsonTest
WHERE JSON_CONTAINS(JsonData,'David','$.Name.*') = 1;
you can specify wildcards in the path

This means we’re searching for David in any of the properties of the Name object, instead of only searching in the FirstName property. However, using the path ‘$ .*’ isn’t an alternative for searching the entire document. This path will instruct JSON_CONTAINS to search on the first nodes of the root of the JSON document. The following query won’t return a match, since ‘David’ is text and the Name object is of the JSON type.

using path

On the other hand, if we search for the integer 11000 we do get a match since this value is found in the CustomerKey property which is a node of the root.

search for customerkey

There’s no implicit conversion of data types when using JSON_CONTAINS. If we would search for the string ‘11000’ no match is found since the CustomerKey is stored as an integer in the JSON document.

no implicit conversion

Search Arrays

It’s also possible to search arrays. Suppose we have the following JSON document:

{
   "a": [1,2,3,4],
   "b": [5,6],
   "c": [7,8,[9,10]]
}

If we want to search the array a for the value 1, we can use the following path:

--MSSQLTips.com
DECLARE @myjson AS JSON = '{"a": [1,2,3,4], "b":[5,6], "c": [7, 8, [9,10]]}';
SELECT value_found = JSON_CONTAINS(@myjson, 1,'$.*[*]');
array search with wildcard

If we would like to search in any of the arrays of the first level, we can use two wildcards:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 1,'$.*[*]');
array search with wildcard

Using numbers, you can also search for values in specific elements of an array. For example, with the following query we’re searching for the number 3 in the first 3 elements of the array a:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 3,'$.a[0 to 2]');

The first element can be inspected by using the ordinal 0:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 3,'$.a[0]');

The last element can be found by using the “last” keyword:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 3,'$.a[last]');
searching in the last element of an array

Nested Arrays

It’s also possible to search inside nested arrays:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 9,'$.c[last][*]');

This can also be combined with multiple wildcards:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 9,'$.c[*][*]');

To search within nested arrays inside all arrays of our JSON document, we can use the following path:

--MSSQLTips.com
SELECT value_found = JSON_CONTAINS(@myjson, 9,'$.*[*][*]');

For more information about JSON paths, check out the documentation.

For many of the examples above, we could’ve also used the JSON_VALUE function. For example, searching for “David” as first name becomes:

--MSSQLTips.com
SELECT FirstName, LastName
FROM dbo.JsonTest
WHERE JSON_VALUE(JsonData,'$.Name.FirstName') = 'David';

Here we are comparing the result of the JSON_VALUE function with the item we’re searching for.

The biggest difference between JSON_CONTAINS and JSON_VALUE for searching values is that JSON_CONTAINS can handle arrays and wildcards, while JSON_VALUE needs a JSON path that points to a scalar value. If you want to handle arrays, you could use JSON_QUERY and then either parse the result or convert the result to a string and use traditional string search methods.

Using our JSON array example from before, we can use JSON_QUERY like this to search if array a contains the number 3:

--MSSQLTips.com
DECLARE @myjson AS JSON = '{"a": [1,2,3,4], "b":[5,6], "c": [7, 8, [9,10]]}';
SELECT [key], [value]
FROM OPENJSON(JSON_QUERY(@myjson,'$.a'))
WHERE [value] = 3;
searching using JSON_QUERY and OPENJSON

Using data type conversion to string we can use the following method:

--MSSQLTips.com
DECLARE @myjson AS JSON = '{"a": [1,2,3,4], "b":[5,6], "c": [7, 8, [9,10]]}';
SELECT value_found = IIF(CONVERT(VARCHAR(100),JSON_QUERY(@myjson, '$.a')) LIKE '%3%',1,0);
searching with JSON_QUERY and the LIKE operator

It’s clear that if we have search requirements that would span multiple nodes or arrays, or even nested arrays, that using JSON_QUERY would become much more complex, while JSON_CONTAINS remains straightforward due to its wildcard capabilities.

Next Steps

Related Posts

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *