Menu

SQL Server JSON_CONTAINS(): Search a JSON Path

JSON_CONTAINS(target, search_value, path) checks whether a SQL scalar value occurs in the JSON value or values selected by a path. It returns 1 for a match, 0 when no value matches, or SQL NULL when an argument is SQL NULL or the path doesn’t resolve to a value. Microsoft lists it as a generally available SQL Server 2025 (17.x) JSON feature and documents support for SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric. It is not supported in Fabric Data Warehouse. Check Microsoft’s JSON feature overview and JSON_CONTAINS platform list for current availability.

Syntax

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

Although the syntax marks path_expression optional, Microsoft’s current limitations state that a path is required. Include one in queries. The target can be a JSON string or native json value. The search value must be a SQL scalar; native json values and object or array fragments returned by JSON_QUERY() aren’t supported as search values.

Compare values using their SQL types

The SQL type of search_value_expression determines how the scalar is compared with JSON. For example, compare an SQL int with a JSON number:

DECLARE @doc json = N'{"order":{"id":1042},"tags":["sql","database"]}';

SELECT JSON_CONTAINS(@doc, 1042, '$.order.id') AS has_order_id;

has_order_id
------------
1

This differs from a JSON_VALUE() predicate, which returns scalar values as text by default. Use JSON_CONTAINS() when type-aware scalar comparison is useful.

Search array elements

When the path points to an array, include a wildcard to search its elements. The function returns 1 if a matching element is found:

DECLARE @doc json = N'{"order":{"id":1042},"tags":["sql","database"]}';

SELECT JSON_CONTAINS(@doc, N'sql', '$.tags[*]') AS has_sql_tag;

has_sql_tag
-----------
1

For nested arrays, put a wildcard at each array level in the path. Automatic array unwrapping is limited to the first level, so use an explicit path such as $.orders[*].items[*].sku when searching nested arrays.

Choose equality or LIKE matching

For character search values, search_mode selects the comparison. The default 0 uses equality; 1 uses SQL LIKE pattern matching:

DECLARE @doc json = N'{"tags":["sql","database"]}';

SELECT JSON_CONTAINS(@doc, N'sq%', '$.tags[*]', 1) AS has_tag_starting_with_sq;

has_tag_starting_with_sq
------------------------
1

search_mode applies only to character search values. It doesn’t turn numeric or Boolean comparisons into pattern matches.

Distinguish a path match from a path check

Use JSON_PATH_EXISTS() to ask whether a path exists, regardless of its value. Use JSON_CONTAINS() to ask whether a value matches at that path. If a path points to an array, a wildcard is needed to test its elements.

For path syntax and version-specific wildcard support, see the SQL Server JSON path guide. For simple path extraction, see JSON_VALUE() and JSON_QUERY(). Browse the SQL Server JSON function guide for the full task-based list.

Advertisement