JSON Path Expressions (SQL Server)

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Use JSON path expressions to reference the properties of JSON objects.

You have to provide a path expression when you call the following functions.

Parts of a path expression

A path expression has two components.

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

  2. The path itself.

Path mode

At the beginning of the path expression, optionally declare the path mode by specifying the keyword lax or strict. The default is lax.

  • In lax mode, the function returns empty values if the path expression contains an error. For example, if you request the value $.name, and the JSON text doesn't contain a name key, the function returns null, but does not raise an error.

  • In strict mode, the function raises an error if the path expression contains an error.

The following query explicitly specifies lax mode in the path expression.

DECLARE @json NVARCHAR(MAX)
SET @json=N'{ ... }'

SELECT * FROM OPENJSON(@json, N'lax $.info')

Path

After the optional path mode declaration, specify the path itself.

  • The dollar sign ($) represents the context item.

  • The property path is a set of path steps. Path steps can contain the following elements and operators.

    • Key names. For example, $.name and $."first name". If the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes.

    • Array elements. For example, $.product[3]. Arrays are zero-based.

    • The dot operator (.) indicates a member of an object. For example, in $.people[1].surname, surname is a child of people.

Examples

The examples in this section reference the following JSON text.

{
    "people": [{
        "name": "John",
        "surname": "Doe"
    }, {
        "name": "Jane",
        "surname": null,
        "active": true
    }]
}

The following table shows some examples of path expressions.

Path expression Value
$.people[0].name John
$.people[1] { "name": "Jane", "surname": null, "active": true }
$.people[1].surname null
$ { "people": [ { "name": "John", "surname": "Doe" },
{ "name": "Jane", "surname": null, "active": true } ] }

How built-in functions handle duplicate paths

If the JSON text contains duplicate properties - for example, two keys with the same name on the same level - the JSON_VALUE and JSON_QUERY functions return only the first value that matches the path. To parse a JSON object that contains duplicate keys and return all values, use OPENJSON, as shown in the following example.

DECLARE @json NVARCHAR(MAX)
SET @json=N'{"person":{"info":{"name":"John", "name":"Jack"}}}'

SELECT value
FROM OPENJSON(@json,'$.person.info') 

Learn more about the built-in JSON support in SQL Server

For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

See Also

OPENJSON (Transact-SQL)
JSON_VALUE (Transact-SQL)
JSON_QUERY (Transact-SQL)