title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | dev_langs | ms.custom | monikerRange | ||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
JSON_PATH_EXISTS (Transact-SQL) |
JSON_PATH_EXISTS tests whether a specified SQL/JSON path exists in the input JSON string. |
WilliamDAssafMSFT |
wiassaf |
randolphwest, umajay |
10/31/2024 |
sql |
t-sql |
reference |
|
|
=azuresqldb-current||>=sql-server-ver16||>=sql-server-linux-ver16||=azuresqldb-mi-current |
[!INCLUDE sqlserver2016-asdb-asmi]
Tests whether a specified SQL/JSON path exists in the input JSON string.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
JSON_PATH_EXISTS( value_expression, sql_json_path )
A character expression.
A valid SQL/JSON path to test in the input.
Returns a 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.
The following example returns 1 since the input JSON string contains the specified SQL/JSON path.
DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';
SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.address'); -- 1
The following example returns 0 since the input JSON string doesn't contain the specified SQL/JSON path.
DECLARE @jsonInfo NVARCHAR(MAX)
SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';
SELECT JSON_PATH_EXISTS(@jsonInfo,'$.info.addresses'); -- 0