Skip to content

json-path-expressions-sql-server.md

Latest commit

 

History

History
124 lines (86 loc) · 5.32 KB

json-path-expressions-sql-server.md

File metadata and controls

124 lines (86 loc) · 5.32 KB
title JSON Path Expressions (SQL Server) | Microsoft Docs
ms.custom
ms.date 01/23/2017
ms.prod sql
ms.reviewer genemi
ms.technology
ms.topic conceptual
helpviewer_keywords
JSON, path expressions
path expressions (JSON)
ms.assetid 25ea679c-84cc-4977-867c-2cbe9d192553
author jovanpop-msft
ms.author jovanpop
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

JSON Path Expressions (SQL Server)

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

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.

[!INCLUDEfreshInclude]

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 JSON in SQL Server and Azure SQL Database

Microsoft videos

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

See Also

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