Introduction to the JSON_PATH_EXISTS() Function in SQL Server

by

From SQL Server 2022 we can use the JSON_PATH_EXISTS() function to test whether a specified SQL/JSON path exists in the input JSON string.

It’s similar to the JSON_CONTAINS_PATH() function that’s available in MySQL and MariaDB.

Syntax

The syntax goes like this:

JSON_PATH_EXISTS( value_expression, sql_json_path )

Where value_expression is a character expression, and sql_json_path is the SQL/JSON path to test in the input.

Example

Here’s an example to demonstrate:

DECLARE @json_document nvarchar(max);

SET @json_document=N'{ "name": "Wag", "weight": 10 }';

SELECT JSON_PATH_EXISTS(@json_document, '$.name');

Result:

1

In this case the path exists and so 1 is returned.

Here’s what happens when the path doesn’t exist:

DECLARE @json_document nvarchar(max);

SET @json_document=N'{ "name": "Wag", "weight": 10 }';

SELECT JSON_PATH_EXISTS(@json_document, '$.height');

Result:

0

We get zero because the path doesn’t exist.

Passing a NULL Input Expression

Passing a NULL input expression results in NULL being returned:

JSON_PATH_EXISTS(null, '$.height');

Result:

NULL

Passing the Wrong Number of Arguments

Passing the wrong number of arguments results in an error:

JSON_PATH_EXISTS('$.height');

Result:

Msg 174, Level 15, State 1, Server d6c3fe112f7a, Line 5
The json_path_exists function requires 2 argument(s).

In this case I only passed one argument when I should have passed two. The same error occurs when we pass three or more arguments.