SQL Server JSON_PATH_EXISTS() Function

Summary: in this tutorial, you will learn how to use the SQL Server JSON_PATH_EXISTS() function to test if a specified JSON path exists in a JSON string.

Introduction to SQL Server JSON_PATH_EXISTS() function

The JSON_PATH_EXISTS() function tests whether a specified JSON path exists in a JSON string:

JSON_PATH_EXISTS( value, path )Code language: SQL (Structured Query Language) (sql)

In this syntax:

• value is a JSON string that you want to check for a path.
• path is a valid JSON path to test the input string.

The JSON_PATH_EXISTS() function returns 1 if the path exists in the value or 0 otherwise.

SQL Server JSON_PATH_EXISTS function examples

Let’s take some examples of using the JSON_PATH_EXISTS() function.

1) Basic JSON_PATH_EXISTS() function examples

The following example uses the JSON_PATH_EXISTS() function to check if the path '$.name' exists in a JSON string:

SELECT 
  JSON_PATH_EXISTS(
    '{"name": "John", "age": 22}', '$.name'
  ) path_exists;Code language: SQL (Structured Query Language) (sql)

Output:

path_exists
-----------
1Code language: SQL (Structured Query Language) (sql)

In this example, the JSON path $.name, which references the name property of the top-level object, exists in the JSON string; therefore, the function JSON_PATH_EXISTS() returns 1.

The following example uses the JSON_PATH_EXISTS() function to test whether the path '$.email' exists in a JSON string:

SELECT 
  JSON_PATH_EXISTS(
    '{"name": "John", "age": 22}', '$.email'
  ) path_exists;Code language: SQL (Structured Query Language) (sql)

Output:

path_exists
-----------
0Code language: SQL (Structured Query Language) (sql)

Since the $.email does not exist in the input JSON string, the JSON_PATH_EXISTS() function returns 0.

2) Using JSON_PATH_EXISTS() function with table data

First, create a new table called configurations:

CREATE TABLE configurations (
    id INT IDENTITY PRIMARY KEY,
    config NVARCHAR(MAX)
);Code language: SQL (Structured Query Language) (sql)

The config column will store the JSON data.

Second, insert rows into the configurations table:

INSERT INTO configurations (config) 
VALUES
('{"name": "John", "age": 30, "address": {"city": "New York", "zipcode": "10001"}}'),
('{"name": "Alice", "age": 25, "address": {"city": "Los Angeles", "zipcode": "90001"}}'),
('{"name": "Bob", "age": 35}');Code language: SQL (Structured Query Language) (sql)

Third, check if the path $.address.city exists in the JSON data:

SELECT 
  id, 
  JSON_PATH_EXISTS(config, '$.address.city') AS city_exists,
  config
FROM 
  configurations;Code language: SQL (Structured Query Language) (sql)

Output:

id | city_exists | config
---------------------------------------------------------------------------------------------------
1  | 1           | {"name": "John", "age": 30, "address": {"city": "New York", "zipcode": "10001"}}
2  | 1           | {"name": "Alice", "age": 25, "address": {"city": "Los Angeles", "zipcode": "90001"}}
3  | 0           | {"name": "Bob", "age": 35}Code language: SQL (Structured Query Language) (sql)

Summary

• Use the SQL Server JSON_PATH_EXISTS() function to test if a JSON path exists in a JSON string.