SQL - JSON_PATH_EXISTS() Function

---

---

Similar to XPath for XML, JSONPath is a query language for JSON. You can choose and extract data from a JSON document using it. The path to an element in the JSON structure is traversed using a JSONPath expression.

The SQL JSON_PATH_EXISTS() in SQL is used to determine whether a particular JSON route is present within a JSON string.When the path is found, the function returns a bit value of 1, else it returns 0.

Syntax

Following is the syntax of the SQL JSON_PATH_EXISTS() function −

JSON_PATH_EXISTS( value_expression, sql_json_path )

Parameters

• value_expression − It represent a character expression.

• sql_json_path − It represents valid SQL/JSON path to test in the input.

Example

Let's look into the following example which returns 1 as the input input JSON string contains the specified path −

DECLARE @work VARCHAR(MAX)
SET @work=N'{"info":{"salary":[{"Ramesh":"20000"},{"suresh":"300000"}]}}';
SELECT JSON_PATH_EXISTS(@work,'$.info.salary') AS Result; 

Output

When we execute the above query, the output is obtained as follows −

+---------------------------------------------------------+
|                                                 Result  |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+

Example

Let's look into the another scenario where the output returns 0 as the input JSON string doesn't contains the specified path −

DECLARE @work VARCHAR(MAX)
SET @work=N'{"info":{"salary":[{"Ramesh":"20000"},{"suresh":"300000"}]}}';
SELECT JSON_PATH_EXISTS(@work,'$.info.salaries') AS Result; 

Output

On executing the above query, the output is displayed as follows −

+----------------------------------------------+
|                                       Result |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+