Understanding JSON_CONTAINS() in SQL Server 2025

JSON_CONTAINS() is a new function introduced in SQL Server 2025 that lets you check whether a value exists at a specific path in a JSON document. It returns an integer. This is 1 if the value is found, 0 if not, and NULL if any argument is null or the path doesn’t exist. That makes it a natural fit for WHERE clauses when you’re filtering rows based on JSON content.

It’s currently in preview (although this will almost certainly change soon) and only available from SQL Server 2025.

Syntax

The syntax goes like this:

JSON_CONTAINS( 
    target_expression , 
    search_value_expression 
    [ , path_expression ]  
    [ , search_mode ] 
    )

Basically this means that you need to provide the JSON document, and the value to find. The other two arguments are optional. Here’s an explanation of each argument:

target_expression is the JSON document to search – either a JSON typed column or a character string containing JSON.
search_value_expression is the SQL scalar value you’re looking for.
path_expression (optional) is the SQL/JSON path to search within.
search_mode (optional) controls whether matching uses equality (0, the default) or LIKE pattern semantics (1).

Basic Usage

Here’s a straightforward lookup that finds whether a specific integer value exists at a given path:

DECLARE @doc AS JSON = '{"status": "active", "priority": 3, "retries": 0}';

SELECT JSON_CONTAINS(@doc, 3, '$.priority') AS found;

Result:

String lookup works the same way:

DECLARE @doc AS JSON = '{"status": "active", "priority": 3, "retries": 0}';

SELECT JSON_CONTAINS(@doc, 'active', '$.status') AS found;

Result:

Type Matching and Why it Matters

One of the more important things to understand about JSON_CONTAINS() is that it compares using the SQL type of the search value, not a string representation. This is different from JSON_VALUE(), which always returns a character string, meaning comparisons against it are always string comparisons.

In practice that means if you’re checking a JSON boolean, you pass a BIT:

DECLARE @doc AS JSON = '{"active": true, "archived": false}';

SELECT JSON_CONTAINS(@doc, CAST(1 AS BIT), '$.active') AS found;

Result:

The following won’t match, because 'true' as a string is not the same as true as a boolean:

DECLARE @doc AS JSON = '{"active": true, "archived": false}';

SELECT JSON_CONTAINS(@doc, 'true', '$.active') AS found;

Output:

The supported SQL types for the search value are numeric types, character string types, and BIT. You can’t pass a JSON type value as the search value, and you can’t pass the result of JSON_QUERY() either – both are current limitations.

Searching Arrays

When your path points to an array, you need to use the [*] array wildcard in the path expression. Without it, JSON_CONTAINS() won’t find anything inside the array.

DECLARE @doc AS JSON = '{
    "user": "kira",
    "roles": ["editor", "reviewer", "publisher"]
}';

-- Check whether a role exists in the array
SELECT 
    JSON_CONTAINS(@doc, 'reviewer', '$.roles[*]') AS has_reviewer_role,
    JSON_CONTAINS(@doc, 'admin', '$.roles[*]') AS has_admin_role;

Result:

has_reviewer_role  has_admin_role
-----------------  --------------
1                  0

You can also search across all values of a specific key across an array of objects:

DECLARE @pipeline AS JSON = '[
    {"stage": "extract", "enabled": true},
    {"stage": "transform", "enabled": true},
    {"stage": "load", "enabled": false}
]';

SELECT JSON_CONTAINS(@pipeline, 'transform', '$[*].stage') AS found;

Result:

That checked the pipeline to see if any stage was named ‘transform’.

Nested Arrays

Array wildcards can be chained for nested structures:

DECLARE @doc AS JSON = '{
    "build": {
        "steps": [
            {"id": 1, "outputs": ["/dist/app.js", "/dist/app.css"]},
            {"id": 2, "outputs": ["/dist/vendor.js"]}
        ]
    }
}';

SELECT JSON_CONTAINS(@doc, '/dist/app.css', '$.build.steps[*].outputs[*]') AS found;

Result:

This example checks whether a specific output file exists anywhere in the build steps.

Current automatic unwrapping only works at the first level, so for deeper nesting you need to express the full path explicitly.

Pattern Matching with `search_mode`

By default JSON_CONTAINS() uses equality matching. Setting search_mode to 1 switches to LIKE semantics for string values, letting you use wildcard patterns:

DECLARE @doc AS JSON = '{
    "tags": ["sql-server-2025", "json", "performance"]
}';

SELECT JSON_CONTAINS(@doc, 'sql-server%', '$.tags[*]', 1) AS found;

Result:

Here, our code finds any tag that starts with ‘sql-server’.

This only applies when the search value is a character string. For numeric or BIT values search_mode has no effect.

Using `JSON_CONTAINS()` in a Table Query

JSON_CONTAINS() works directly in WHERE clauses, making it clean to filter rows without having to CROSS APPLY OPENJSON() and filter the results.

Let’s create a table with JSON data:

CREATE TABLE pipeline_runs (
    run_id      INT         NOT NULL PRIMARY KEY,
    run_config  JSON        NOT NULL
);

INSERT INTO pipeline_runs VALUES
(101, '{"env": "production", "features": ["logging", "alerting", "tracing"]}'),
(102, '{"env": "staging",    "features": ["logging"]}'),
(103, '{"env": "production", "features": ["logging", "alerting"]}');

Now we’ll run a query that returns all runs in production that have tracing enabled:

SELECT run_id
FROM pipeline_runs
WHERE JSON_CONTAINS(run_config, 'production', '$.env') = 1
  AND JSON_CONTAINS(run_config, 'tracing', '$.features[*]') = 1;

Result:

For comparison, the equivalent using OPENJSON() is a little more verbose:

SELECT DISTINCT p.run_id
FROM pipeline_runs p
CROSS APPLY OPENJSON(CAST(run_config AS NVARCHAR(MAX)), '$.features') f
WHERE JSON_VALUE(run_config, '$.env') = 'production'
  AND f.value = 'tracing';

Result:

Both return the same result, but the JSON_CONTAINS() version is slightly easier to read and expresses intent more directly.

JSON Indexes

SQL Server 2025 also introduces a new CREATE JSON INDEX statement, which is a separate index structure you can create on a JSON typed column to speed up queries against it. It’s worth knowing about in the context of JSON_CONTAINS() because the two features are designed to work together.

Here’s an example of creating a JSON index:

CREATE JSON INDEX ix_pipeline_config ON pipeline_runs(run_config)
FOR ('$.env', '$.features');

The FOR clause specifies which paths to index. You can only create one JSON index per column, but you can include as many paths as you need within it. Microsoft’s documentation lists JSON_CONTAINS(), JSON_VALUE(), and JSON_PATH_EXISTS() as the functions that can benefit from a JSON index.

A few things to know before going all-in on them:

First, the column must be the native JSON data type. You can’t create a JSON index on a VARCHAR(MAX) or NVARCHAR(MAX) column, so JSON indexes are only relevant if you’ve already adopted the JSON type.
The table also needs a clustered primary key.
Only one JSON index is allowed per column, although you can include multiple paths within it.
Up to 249 JSON indexes are allowed per table.

There are also some rough edges worth knowing about before adopting JSON indexes. Index sizes can end up several times larger than the underlying data, and there’s a consistent behaviour in query plans where the engine always joins back to the clustered index (even when the JSON index should contain everything needed to satisfy the query). Brent Ozar’s testing found this to be the case, and Daniel Hutmacher’s first impressions observed the same thing. The optimizer doesn’t appear to treat a JSON index as a covering index under any circumstances tested.

In practice, JSON_CONTAINS() is currently the function that gets meaningful query speed improvements, while JSON_VALUE()-based queries show inconsistent results despite the documentation listing it as supported. If you’re already using indexed computed columns against a NVARCHAR(MAX) column, that approach may be more predictable at this stage. JSON indexes are a feature worth watching as they mature.

`JSON_CONTAINS()` Limitations

The following limitations are documented by Microsoft in the JSON_CONTAINS() reference:

The path parameter is technically documented as optional (defaulting to $), but in practice the current build doesn’t return results when the path is omitted. Might be best to treat it as required for now.
You can’t use a JSON typed value or the result of JSON_QUERY() as the search value. Both are unsupported.
When a path points to an array, the wildcard is required. Automatic array unwrapping currently only works at the first level, so deeper nesting requires explicit wildcard chaining in the path.
JSON_CONTAINS() is only available in SQL Server 2025. It isn’t available on Azure SQL Database, Managed Instance, or Fabric yet (although this could have changed by the time you read this).

Syntax

Basic Usage

Type Matching and Why it Matters

Searching Arrays

Nested Arrays

Pattern Matching with search_mode

Using JSON_CONTAINS() in a Table Query

JSON Indexes

JSON_CONTAINS() Limitations

Pattern Matching with `search_mode`

Using `JSON_CONTAINS()` in a Table Query

`JSON_CONTAINS()` Limitations