Edit

Share via

JSON_PATH_EXISTS (Transact-SQL)

  • Article

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

Tests whether a specified SQL/JSON path exists in the input JSON string.

Transact-SQL syntax conventions

Syntax

syntaxsql 
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.

SQL 
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.

Output 
1

Example 2

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

SQL 
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.

Output 
0

Related content