通过

JSON_QUERY（Transact-SQL）

适用于：sql Server 2016 （13.x） 及更高版本 Azure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsSQL 分析终结点Microsoft FabricWarehouse 中的Microsoft Fabric SQL 数据库在 Microsoft Fabric 中的 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

表达式。 通常是包含 JSON 文本的变量或列的名称。

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

path

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

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

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

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

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

使用数组包装器

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 AS JSON = '{
    "id": 2,
    "first_name": "Mamie",
    "last_name": "Baudassi",
    "email": "mbaudassi1@example.com",
    "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, '$.credit_cards[*].type' WITH ARRAY WRAPPER);

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

Path 返回值
$.credit_cards[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"]

返回值

返回 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 的行为进行了比较。 有关可选路径模式规范（宽松或严格）的详细信息，请参阅 SQL 数据库引擎中的 JSON 路径表达式。

Path 宽松模式下的返回值 严格模式下的返回值 详细信息
$ 返回整个 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_QUERY参数的 JSON 数据进行包装。

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@example.com", "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"]

相关内容

  • Last updated on