SQL Server's JSON_PATH_EXISTS function is used to check whether a specific JSON path exists within a JSON string. The function returns a bit value of 1 if the path exists, and 0 if it does not.

Syntax

The syntax for JSON_PATH_EXISTS is as follows:

JSON_PATH_EXISTS ( json_string , path )

Where json_string is the JSON string to search and path is the JSON path to look for.

Example

For example, if we have a JSON string like this:

{
"name": "John Doe",
"age": 30,
"address": {
"city": "New York",
"state": "NY",
"country": "USA"
}
}

We can use JSON_PATH_EXISTS to check if a particular path exists, like so:

SELECT JSON_PATH_EXISTS('{
"name": "John Doe",
"age": 30,
"address": {
"city": "New York",
"state": "NY",
"country": "USA"
}
}', '$.name') as NameExists,
JSON_PATH_EXISTS('{
"name": "John Doe",
"age": 30,
"address": {
"city": "New York",
"state": "NY",
"country": "USA"
}
}', '$.address.postcode') as PostcodeExists

This will return the following result:

In this example, the first call to JSON_PATH_EXISTS checks if the path $name exists within the JSON string. Since the path does exist, the function returns 1. The second call checks if the path $address.postcode exists, which it does not, so the function returns 0.

In conclusion, JSON_PATH_EXISTS is a useful function for checking the existence of specific JSON paths within a JSON string in SQL Server.