通过

JSON_QUERY（Transact-SQL）

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

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

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

Transact-SQL 语法约定

语法

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

参数

expression

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

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

路径

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

在 SQL Server 2017 (14.x) 和 Azure SQL 数据库 中，可提供变量作为 path 的值。

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

路径的默认值为 $. 因此，如果没有为 path 提供值，则 JSON_QUERY 会返回输入 expression。

如果 path 格式无效，则 JSON_QUERY 返回错误。

WITH ARRAY WRAPPER

注释

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 路径表达式的各种示例。

路径 返回值
$.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"]

返回值

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

如果值不是对象或数组：

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

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

注解

宽松模式和严格模式

请参考以下 JSON 文本：

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

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

路径 宽松模式下的返回值 严格模式下的返回值 更多信息
$ 返回整个 JSON 文本。 返回整个 JSON 文本。
$.info.type NULL 错误 不是对象或数组。

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

请改用 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 错误 不是数组。
$.info.none NULL 错误 属性不存在。

将 JSON_QUERY 与 FOR JSON 结合使用

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

如果在使用 FOR JSON 返回结果，并且包含已采用 JSON 格式（在列中或作为表达式的结果）的数据，则使用不带 pathJSON_QUERY 参数的 对数据进行包装。

示例

答： 返回 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"]

相关内容