Determine if a Specific Path in a JSON Document Exists in SQL Server

By: Daniel Calbimonte   |   Updated: 2023-06-08   |   Comments   |   Related: > Functions System

Problem

JSON_PATH_EXISTS is a new function in SQL Server 2022 that allows you to determine if a specific path exists in a JSON document. We will walk through how to use this function in this article.

Solution

Let's take a look at how to use JSON_PATH_EXISTS in SQL Server 2022 and later.

JSON_PATH_EXISTS Function Syntax

The syntax is the following:

JSON_PATH_EXISTS (value or expression, path)

The function returns 0 if it is an invalid JSON path and 1 if it is a valid JSON path.

Where value or expression is the JSON document and path the part of the JSON document you want to see if it exists. The path is searched in the value; if it exists, it will return 1 (true). If the path does not exist inside the value or expression, it will return the value 0.

Sample JSON Data

Let’s take a look at some examples to understand how it works.

To understand the function, I will use a hierarchical structure. The data will include the mother Rhaenrya Targaryen from the House of the Dragon and two children Jacaerys and Lucerys.

The JSON data is the following:

{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}
';

JSON_PATH_EXISTS Example

First, we will verify if the path for the mother's name exists.

The following example verifies if the path for the Rhaenyra’s name attribute exists in the JSON document.

DECLARE @jsonsample NVARCHAR(MAX)
 
SET @jsonsample=N'{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}
';
 
SELECT JSON_PATH_EXISTS(@jsonsample,'$.name') exist

The result is false (0).

Why is that? We created a variable named @jsonsample which contains the JSON document with the data. It contains the mother’s name Rhaenyra. This is because the function is case-sensitive and the name path is uppercase in the JSON document:

"Name":"Rhaenyra",

To fix this problem, we need to modify the function to capitalize Name like this:

SELECT JSON_PATH_EXISTS(@jsonsample,'$.Name') exist

The full code is:

DECLARE @jsonsample NVARCHAR(MAX)
 
SET @jsonsample=N'{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}
';
 
SELECT JSON_PATH_EXISTS(@jsonsample,'$.Name') exist

The code will provide the following results:

Note: The $ is the root used by the function:

'$.Name'

You could search for the existence of last name like this:

'$.Lastname'

And the children like this:

'$.Children'

JSON_PATH_EXISTS Example with Hierarchies

How can we verify the attributes of Rhaenyra’s children?

The following example will verify the path for the children:

DECLARE @jsonsample NVARCHAR(MAX)
 
SET @jsonsample=N'{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}
';
 
SELECT JSON_PATH_EXISTS(@jsonsample,'$.Children[0].name') exist

The path will be valid:

Note: We specified the first child with the number 0:

SELECT JSON_PATH_EXISTS(@jsonsample,'$.Children[0].name') exist

If we wanted to verify the second child, the code would be the following:

SELECT JSON_PATH_EXISTS(@jsonsample,'$.Children[1].name') exist

However, if we try to verify if the path exists for a third child, the function will return a false value (0).

SELECT JSON_PATH_EXISTS(@jsonsample,'$.Children[2].name') exist

Common Errors for the JSON_PATH_EXISTS Function

The following syntax will show an error message if we try to find if the children name path exists:

SELECT JSON_PATH_EXISTS(@jsonsample,'$..name') exist

A common error is this one:

To fix the problem, write the full path:

SELECT JSON_PATH_EXISTS(@jsonsample,'$.Children[0].name') exist

JSON_PATH_EXISTS is Not a Recognized Built-in Function Name

If we try to run this in versions before SQL Server 2022 we will get an error.

DECLARE @jsonsample NVARCHAR(MAX)
 
SET @jsonsample=N'{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}
';
SELECT JSON_PATH_EXISTS(@jsonsample,'$.Name') exist

Conclusion

In this tip, we learned how to work with the new JSON_PATH_EXISTS function. The function verifies if the JSON path exists and returns the value of 1 (true) and 0 (false) if the path does not exist. Finally, we saw some typical error messages.

Next Steps

• Related article: ISJSON enhancements in SQL Server for valid JSON format for a Value, Array or Object

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips