通过

JSON 路径表达式 (SQL Server)

适用范围：SQL Server 2016 (13.x) 及更高版本Azure SQL 数据库Azure SQL 托管实例Azure 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].surname 中，surname 是 people 的子级。

    • 如果输入是 JSON 类型值，则还支持数组通配符和范围搜索。

数组通配符和区间支持

注意

数组通配符和范围支持目前为预览版，仅在 SQL Server 2025（17.x） 预览版中可用。

SQL Server 2025 （17.x） 预览版扩展 ANSI SQL/JSON 路径表达式以支持数组通配符。 通过数组通配符，可以指定所有元素、元素范围、元素列表或特殊标记“last”来指示 JSON 数组中的最后一个值。 SQL/JSON 数组使用从零开始的索引。 可以在 JSON_QUERY、 JSON_PATH_EXISTS和 JSON_CONTAINS中使用带通配符的 SQL/JSON 路径。

虽然 JSON_VALUE 函数支持 SQL/JSON 路径表达式，但函数的 JSON_VALUE 返回值是 SQL 标量，因此该函数始终返回 NULL 指向 JSON 对象或数组的任何 SQL/JSON 路径。 仅当输入为 json 类型时，才支持数组通配符。

以下语法演示如何使用通配符、范围和特殊标记 last ：

path[elements ]

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

特殊令牌 last 可用于代替数字值。 如果指定了范围，则需要按递增顺序指定范围。

一些有效的 SQL/JSON 路径表达式的示例：

路径 DESCRIPTION
$[*] 所有元素
$[0] 第一个元素
$[0 to 2] 前三个元素
$[last] 最后一个元素
$[last, 0] “无效”
$[last, 2, 0, last] “无效”
$.creditcards[0].type 返回数组中 creditcards 第一个元素的类型属性值
$.credit_cards[*].type 返回数组中所有元素的类型 creditcards 属性值
$.credit_cards[0, 2].type 返回数组中第一个和第三个元素的类型 creditcards 属性值
$.credit_cards[1 to 3].type 返回数组中第二到第四个元素的类型 creditcards 属性值
$.credit_cards[last].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');

详细了解 SQL Server 和 Azure SQL 数据库中的 JSON

有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视觉简介，请参阅以下视频：

相关内容