Rediger

JSON_PATH_EXISTS (Transact-SQL)

Applies to: SQL Server 2022 (16.x) and later versions Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL analytics endpoint in Microsoft Fabric and Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

The JSON_PATH_EXISTS syntax tests whether a specified SQL/JSON path exists in the input JSON string.

Transact-SQL syntax conventions

Syntax

JSON_PATH_EXISTS( value_expression , sql_json_path )

Arguments

value_expression

A character expression.

sql_json_path

A valid SQL/JSON path to test in the input.

Return value

Returns an int value of 1 or 0 or NULL. Returns NULL if the value_expression or input is a SQL NULL value. Returns 1 if the given SQL/JSON path exists in the input or returns a non-empty sequence. Returns 0 otherwise.

The JSON_PATH_EXISTS function doesn't return errors.

Examples

Example 1

The following example returns 1 since the input JSON string contains the specified SQL/JSON path. This example uses a nested path where the key is present in another object.

DECLARE @jsonInfo AS NVARCHAR (MAX);

SET @jsonInfo = N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo, '$.info.address');

Here's the result set.

1

Example 2

The following example returns 0 since the input JSON string doesn't contain the specified SQL/JSON path.

DECLARE @jsonInfo AS NVARCHAR (MAX);

SET @jsonInfo = N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo, '$.info.addresses');

Here's the result set.

0

Example 3

The following example uses JSON_PATH_EXISTS() with a wildcard:

DECLARE @jsonInfo AS NVARCHAR (MAX);

SET @jsonInfo = N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo, '$.info.address[*].town'); -- Returns: 1

Here's the result set.

1

The following looks for at least one element in array has an object with key town, and finds one.

SET @jsonInfo = N'{"info":{"address":[{"town":"Paris"},{"city":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo, '$.info.address[*].town'); -- Returns: 1  (at least one element in array has an object with key "town")

Here's the result set.

1

The following looks for at least one element in array has an object with key town, but finds none.

SET @jsonInfo = N'{"info":{"address":[{"city":"Paris"},{"city":"London"}]}}';

SELECT JSON_PATH_EXISTS(@jsonInfo, '$.info.address[*].town'); -- Returns: 0 (no elements in array has an object with key "town")

Here's the result set.

0

Related content

  • Last updated on