通过

JSON_QUERY (Transact-SQL)

适用于：SQL Server 2016 （13.x）及更高版本 Azure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 分析终结点Microsoft Fabric 中的仓库Microsoft Fabric 预览版中的 SQL 数据库

语法 JSON_QUERY 从 JSON 字符串中提取对象或数组。

若要从 JSON 字符串而不是对象或数组中提取标量值，请参阅 JSON_VALUE。 有关差异JSON_VALUEJSON_QUERY的信息，请参阅比较JSON_VALUE和JSON_QUERY。

Transact-SQL 语法约定

Syntax

JSON_QUERY ( expression [ , path ] [WITH ARRAY WRAPPER])

Arguments

expression

An expression. 通常是包含 JSON 文本的变量或列的名称。

如果在JSON_QUERY找到路径标识的值之前在表达式中找不到无效的 JSON，则函数将返回错误。 如果 JSON_QUERY 找不到 路径标识的值，它将扫描整个文本，如果发现 表达式中的任何位置都无效的 JSON，则会返回错误。

path

指定要提取的对象或数组的 JSON 路径。

在 SQL Server 2017（14.x）和 Azure SQL 数据库中，可以将变量作为 路径的值提供。

JSON 路径可以为分析指定宽松或严格模式。 如果未指定分析模式，则宽松模式是默认值。 有关详细信息，请参阅 JSON 路径表达式。

路径的默认值为 $. 因此，如果未为 路径提供值， JSON_QUERY 则返回输入 表达式。

如果 路径 的格式无效， JSON_QUERY 则返回错误。

WITH ARRAY WRAPPER

Note

WITH ARRAY WRAPPER 目前为预览版，仅在 SQL Server 2025（17.x） 预览版中可用。

ANSI SQL JSON_QUERY 函数当前用于返回指定路径中的 JSON 对象或数组。 借助 SQL Server 2025 （17.x） 预览版中引入的 SQL/JSON 路径表达式中的 数组通配符 的支持， JSON_QUERY 可用于返回 JSON 数组中每个元素都是 JSON 对象的元素的指定属性。 由于通配符搜索可以返回多个值，因此请在 WITH ARRAY WRAPPER JSON 查询表达式中指定子句以及带有通配符或范围或列表的 SQL/JSON 路径表达式，以将值作为 JSON 数组返回。 WITH ARRAY WRAPPER 仅当输入是 json 类型时，才支持子句。

请考虑以下 JSON 文档：

declare @j JSON = '{
    "id": 2,
    "first_name": "Mamie",
    "last_name": "Baudassi",
    "email": "mbaudassi1@abc.net.au",
    "gender": "Female",
    "ip_address": "148.199.129.123",
    "credit_cards": [
        {
            "type": "jcb",
            "card#": "3545138777072343",
            "currency": "Koruna"
        },
        {
            "type": "diners-club-carte-blanche",
            "card#": "30282304348533",
            "currency": "Dong"
        },
        {
            "type": "jcb",
            "card#": "3585303288595361",
            "currency": "Yuan Renminbi"
        },
        {
            "type": "maestro",
            "card#": "675984450768756054",
            "currency": "Rupiah"
        },
        {
            "type": "instapayment",
            "card#": "6397068371771473",
            "currency": "Euro"
        }
    ]
}';

路径 $.credit_cards 指向 JSON 数组，其中每个元素都是有效的 JSON 对象。 现在，该 JSON_QUERY 函数可用于数组通配符支持，以返回属性的所有或特定值 type ，例如：

SELECT JSON_QUERY(@j, '$.creditcards[*].type' WITH ARRAY WRAPPER);

下表显示了包含通配符和返回值的 JSON_QUERY WITH ARRAY WRAPPERSQL/JSON 路径表达式的各种示例。

Path Return value
$.creditcards[0].type ["jcb"]
$.credit_cards[*].type ["jcb","diners-club-carte-blanche","jcb","maestro","instapayment"]
$.credit_cards[0, 2].type ["jcb","jcb"]
$.credit_cards[1 to 3].type ["diners-club-carte-blanche","jcb","maestro"]
$.credit_cards[last].type ["instapayment"]
$.credit_cards[last, 0].type ["instapayment","jcb"]
$.credit_cards[last, last].type ["instapayment","instapayment"]
$.credit_cards[ 0, 2, 4].type ["jcb","jcb","instapayment"]

Return value

返回 nvarchar（max）类型的 JSON 片段。 返回值的排序规则与输入表达式的排序规则相同。

如果值不是对象或数组：

  • 在宽松模式下，JSON_QUERY 返回 NULL。

  • 在严格模式下，JSON_QUERY 返回错误。

Remarks

宽松模式和严格模式

请参考以下 JSON 文本：

{
   "info": {
      "type": 1,
      "address": {
         "town": "Cheltenham",
         "county": "Gloucestershire",
         "country": "England"
      },
      "tags": ["Sport", "Water polo"]
   },
   "type": "Basic"
}

下表对宽松模式和严格模式下 JSON_QUERY 的行为进行了比较。 有关可选路径模式规范（宽松或严格）的详细信息，请参阅 JSON 路径表达式。

Path 宽松模式下的返回值 严格模式下的返回值 More info
$ 返回整个 JSON 文本。 返回整个 JSON 文本。
$.info.type NULL Error 不是对象或数组。

请改用 JSON_VALUE。
$.info.address.town NULL Error 不是对象或数组。

请改用 JSON_VALUE。
$.info."address" N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }' N'{ "town":"Cheltenham", "county":"Gloucestershire", "country":"England" }'
$.info.tags N'[ "Sport", "Water polo"]' N'[ "Sport", "Water polo"]'
$.info.type[0] NULL Error 不是数组。
$.info.none NULL Error 属性不存在。

将 JSON_QUERY 与 FOR JSON 结合使用

JSON_QUERY 返回有效 JSON 片段。 因此，FOR JSON 不会转义 JSON_QUERY 返回值中的特殊字符。

如果要使用 FOR JSON 返回结果，并且包含已采用 JSON 格式的数据（在列或表达式的结果中），请使用不带路径参数的 JSON 数据JSON_QUERY进行包装。

Examples

A. 返回 JSON 片段

下面的示例演示如何在查询结果中从 CustomFields 列返回 JSON 片段。

SELECT PersonID,
       FullName,
       JSON_QUERY(CustomFields, '$.OtherLanguages') AS Languages
FROM Application.People;

B. 在 FOR JSON 输出中包含 JSON 片段

下面的示例演示如何在 FOR JSON 子句的输出中包含 JSON 片段。

SELECT StockItemID,
       StockItemName,
       JSON_QUERY(Tags) AS Tags,
       JSON_QUERY(CONCAT('["', ValidFrom, '","', ValidTo, '"]')) AS ValidityPeriod
FROM Warehouse.StockItems
FOR JSON PATH;

C. 将 WITH ARRAY WRAPPER 与 JSON_QUERY 函数配合使用

以下示例演示如何使用WITH ARRAY WRAPPERJSON_QUERY函数从 JSON 数组返回多个元素：

DECLARE @j JSON = ' {"id":2,"first_name":"Mamie","last_name":"Baudassi","email":"mbaudassi1@abc.net.au","gender":"Female","ip_address":"148.199.129.123","credit_cards":[{"type":"jcb","card#":"3545138777072343","currency":"Koruna"},{"type":"diners-club-carte-blanche","card#":"30282304348533","currency":"Dong"},{"type":"jcb","card#":"3585303288595361","currency":"Yuan Renminbi"},{"type":"maestro","card#":"675984450768756054","currency":"Rupiah"},{"type":"instapayment","card#":"6397068371771473","currency":"Euro"}]}
';
SELECT JSON_QUERY(@j, '$.credit_cards[*].type' WITH ARRAY WRAPPER ) as credit_card_types;

结果集如下。

credit_card_types
--------
["jcb","diners-club-carte-blanche","jcb","maestro","instapayment"]

Related content