Menu

SQL Server JSON_PATH_EXISTS(): Check a JSON Path

JSON_PATH_EXISTS(value_expression, sql_json_path) tests whether a specified path exists in a JSON string. It returns 1 when the path exists or resolves to a non-empty sequence, 0 when it doesn’t, and SQL NULL when the input expression is SQL NULL. The function was introduced in SQL Server 2022 (16.x). See Microsoft’s JSON_PATH_EXISTS reference.

Syntax

JSON_PATH_EXISTS(value_expression, sql_json_path)

value_expression is a character expression containing JSON text. sql_json_path is the path to check. The function returns an int value and doesn’t raise errors as part of the path-existence check.

Distinguish a missing key from JSON null

A path can exist even when the JSON value at that path is null. This differs from a missing property:

DECLARE @doc nvarchar(max) = N'{"id":17,"nickname":null}';

SELECT
    JSON_PATH_EXISTS(@doc, '$.id') AS has_id,
    JSON_PATH_EXISTS(@doc, '$.nickname') AS has_nickname,
    JSON_PATH_EXISTS(@doc, '$.missing') AS has_missing,
    JSON_PATH_EXISTS(CAST(NULL AS nvarchar(max)), '$.id') AS sql_null_input;

has_id | has_nickname | has_missing | sql_null_input
-------|--------------|-------------|---------------
1      | 1            | 0           | NULL

Use JSON_PATH_EXISTS when you need to tell whether a property is present, regardless of whether its JSON value is null. Use ISJSON() to check whether the input contains a valid top-level JSON object or array; checking syntax and checking a path are different tasks.

Filter rows by a nested path

Use the function in a WHERE clause to select documents that contain a nested property:

SELECT EventId, Payload
FROM dbo.Events
WHERE JSON_PATH_EXISTS(Payload, '$.customer.email') = 1;

The path doesn’t have to point to a scalar. It can identify an object, array, or nested property. For scalar extraction, use JSON_VALUE(); for an object or array fragment, use JSON_QUERY().

Check array elements with a wildcard

A wildcard path can match a property on any array element. The function returns 1 if at least one element matches and 0 if none do:

DECLARE @doc nvarchar(max) = N'{"addresses":[{"town":"Paris"},{"city":"London"}]}';

SELECT JSON_PATH_EXISTS(@doc, '$.addresses[*].town') AS has_any_town;

has_any_town
------------
1

Here, one array item has a town property, so the path matches. This doesn’t assert that every item has that property. For path modes, key quoting, indexes, and wildcard syntax, see the SQL Server JSON path guide. For other functions and SQL Server version differences, see the SQL Server JSON function guide.

Advertisement