Поделиться через

Выражения пути JSON (SQL Server)

Область применения:SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL AzureAzure Synapse Analytics (только бессерверный пул SQL)

Используйте выражения пути JSON для создания ссылок на свойства объектов JSON.

Выражение пути нужно указывать при вызове следующих функций.

  • При вызове OPENJSON для создания реляционного представления данных JSON.
  • При вызове JSON_VALUE с целью извлечения значения из текста JSON.
  • При вызове JSON_QUERY для извлечения объекта JSON или массива.
  • При вызове JSON_MODIFY для обновления значения свойства в строке JSON.

Элементы выражения пути

Выражение пути состоит из двух компонентов.

  1. Необязательный режим пути с значением lax или strict.

  2. Сам путь .

режим пути

В начале выражения пути при необходимости объявите режим пути, указав ключевое слово lax или strict. Значение по умолчанию — lax.

  • В lax режиме функция возвращает пустые значения, если выражение пути содержит ошибку. Например, если вы запрашиваете значение $.name, а текст JSON не содержит name ключ, функция возвращает значение NULL, но не вызывает ошибку.

  • В strict режиме функция вызывает ошибку, если выражение пути содержит ошибку.

В следующем запросе в выражении пути явно задан режим lax.

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'{ ... }';

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

Путь

Объявив необязательный режим пути, укажите сам путь.

  • Знак доллара ($) представляет элемент контекста.

  • Путь свойства — это набор действий пути. Действия пути могут содержать следующие элементы и операторы.

    • Имена ключей. Например, $.name и $."first name". Если имя ключа начинается с знака доллара или содержит специальные символы, такие как пробелы или операторы точек(.), окружают его кавычками.

    • Элементы массива. Например, $.product[3]. Массивы отсчитываются от нуля.

    • Оператор "точка" (.) указывает на элемент объекта. Например, в $.people[1].surnamesurname является дочерним элементом people.

    • Поиск по подстановочным знакам массива и диапазону также поддерживается, если входные данные являются значением типа JSON.

Поддержка подстановочных знаков массива и диапазона

Примечание.

Поддержка подстановочных знаков массива и диапазона в настоящее время доступна в предварительной версии и доступна только в предварительной версии SQL Server 2025 (17.x).

Предварительная версия SQL Server 2025 (17.x) расширяет выражение пути ANSI SQL/JSON для поддержки подстановочного знака массива. Подстановочный знак массива позволяет указать все элементы, диапазон элементов, список элементов или специальный маркер "last" для указания последнего значения в массиве JSON. Массивы SQL/JSON используют отсчитываемый от нуля индекс. Путь SQL/JSON с подстановочными знаками можно использовать в JSON_QUERY, JSON_PATH_EXISTS и JSON_CONTAINS.

Хотя JSON_VALUE функция поддерживает выражение пути SQL/JSON, возвращаемое значение JSON_VALUE функции является скалярным SQL, поэтому функция всегда возвращается NULL для любого пути SQL/JSON, который указывает на объект ИЛИ массив JSON. Подстановочные знаки массива поддерживаются только в том случае, если входные данные являются типом JSON .

В следующем синтаксисе показано, как можно использовать подстановочный знак, диапазон и специальный маркер last :

path[elements ]

elements ::= {
*
| number
| number to number
| last
| {number...[, number] }
}

Специальный маркер last можно использовать вместо значения числа. Если задан диапазон, необходимо указать диапазон в порядке увеличения.

Примеры некоторых допустимых выражений пути SQL/JSON:

Путь Описание
$[*] Все элементы
$[0] Первый элемент
$[0 to 2] Первые три элемента
$[last] Последний элемент
$[last, 0] Недействительное
$[last, 2, 0, last] Недействительное
$.creditcards[0].type Возвращает значение свойства type первого элемента в creditcards массиве
$.credit_cards[*].type Возвращает значение свойства type всех элементов в creditcards массиве
$.credit_cards[0, 2].type Возвращает значение свойства типа первого и третьего элемента в creditcards массиве
$.credit_cards[1 to 3].type Возвращает значение свойства type второго к четвертому элементу в массиве creditcards
$.credit_cards[last].type Возвращает значение свойства type последнего элемента в creditcards массиве
$.credit_cards[last, 0].type Возвращает значение свойства типа последнего и первого элемента в creditcards массиве

Примеры

В примерах этого раздела используется следующий текст JSON.

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

В таблице ниже приведены некоторые примеры выражений пути.

Выражение пути Значение
$.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 } ] }
$.people[last].name ["Jane"]
$.people[0 to 1].name ["John","Jane"]
$.people[0, 1].name ["John","Jane"]

Как встроенные функции обрабатывают повторяющиеся пути

Если текст JSON содержит повторяющиеся свойства , например, два ключа с одинаковым именем на одном уровне — JSON_VALUE и JSON_QUERY функции возвращают только первое значение, соответствующее пути. Чтобы проанализировать объект JSON, содержащий повторяющиеся ключи и возвращать все значения, используйте OPENJSON, как показано в следующем примере.

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'{"person":{"info":{"name":"John", "name":"Jack"}}}';

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

Дополнительные сведения о JSON в SQL Server и базе данных SQL Azure

Визуальные сведения о встроенной поддержке JSON в SQL Server и Базе данных SQL Azure см. в следующем видео:

Связанный контент