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 | +----------------------------------------------+