SQL Server JSON Path Expressions

Summary: in this tutorial, you will learn about JSON path expressions and how to use them to locate elements in a JSON document.

Introduction to the JSON Path Expressions

JSON path expressions offer a way to navigate through JSON documents and allow you to specify the path to specific data elements.

A JSON path expression has two main parts:

• The optional path mode, with a value of lax or strict.
• The path itself.

Path mode

The path mode controls how SQL Server handles non-existent paths. SQL Server supports two path modes: lax and strict.

• lax: in the lax mode, the function returns null if the JSON path expression contains an error or does not exist in the JSON document.
• strict: In the strict mode, the function raises an error if the JSON path expression contains an error or does not exist.

To specify a path mode, use the keyword lax or strict at the beginning of the path expression. The default is lax:

'lax json_path'
'strict json_path'Code language: SQL (Structured Query Language) (sql)

Path

A JSON path is one or a combination of some of the following operators:

In practice, you use JSON path expressions when calling the following JSON functions:

• JSON_VALUE() – Extract a scalar value from a JSON document.
• JSON_QUERY() – Extract an object or an array from a JSON document.
• JSON_MODIFY() – Insert, Update, and Delete elements in a JSON document.
• OPENJSON() – Create a relational view of a JSON document.

JSON Path Expression Examples

We’ll take some examples of using the JSON path expressions.

1) Using JSON Path Expression to extract a property of a JSON object

The following example uses the JSON path expression '$.name' to extract the name property of a JSON object:

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

Output:

name
----
JohnCode language: SQL (Structured Query Language) (sql)

2) Using JSON path expressions with lax or strict mode

The following example uses the JSON path expression '$.email' to extract the email property of a JSON object:

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

Output:

email
-----
NULLCode language: SQL (Structured Query Language) (sql)

In this example, the JSON path expression '$.email' does not exist in the JSON document; therefore, the function returns NULL. It does not cause an error because the path mode defaults to lax.

To instruct SQL Server to use the strict mode, you can use the strict keyword at the beginning of the JSON path expression as follows:

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

Error:

Property cannot be found on the specified JSON path.Code language: SQL (Structured Query Language) (sql)

3) Using JSON path expressions to extract a JSON array element

The following example uses the JSON path expression '$.skills[0]' to extract the first element of an array in a JSON document:

SELECT 
  JSON_VALUE(
    '{"name": "John","age":22, "skill": ["SQL Server","C#"]}', 
    '$.skill[0]'
  ) skill;Code language: SQL (Structured Query Language) (sql)

Output:

skill
----------
SQL ServerCode language: SQL (Structured Query Language) (sql)

Here is the breakdown of the JSON path expression $.skill[0]:

• $ represents the root object.
• .skill accesses the skill property within the root object.
• [0] accesses the first element of the skill array.

So the JSON path expression $.skill[0] selects the value of the first element in the skill array within the root object of the JSON document.

Summary

• Use JSON path expressions to locate elements within a JSON document.