使用内置函数处理 JSON 数据

12 分钟

假设电子商务应用程序将客户首选项和订单元数据存储为 JSON 文档。移动应用以 JSON 格式发送购物车数据，报告系统需要将产品目录导出为 Web API 的 JSON。在数据库中直接使用 JSON 可以消除对应用程序层转换的需求，并使数据处理保持高效。

Fabric 中的 SQL Server、Azure SQL 和 SQL 数据库提供内置的 JSON 支持，可让你直接在 T-SQL 中分析、查询、创建和转换 JSON 数据。在本单元中，你将了解如何使用 JSON 函数提取值、构造 JSON 输出、将数据聚合到 JSON 数组中，以及验证 JSON 内容。

使用JSON_VALUE和JSON_QUERY提取值

使用数据库中存储的 JSON 时，需要提取用于筛选、联接或显示的特定值。 SQL Server 为此提供了两个函数：

JSON_VALUE() 从 JSON 字符串中提取标量值（字符串、数字、布尔值）：

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

结果集将为：

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

该函数使用路径表达式导航 JSON 结构，并将值作为 NVARCHAR(4000) 字符串返回。可以根据需要将结果转换为其他数据类型，以便进行计算或比较。

JSON_QUERY() 提取 JSON 对象或数组（非标值）：

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

结果集将为：

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

与保留 JSON 结构不同 JSON_VALUE()， JSON_QUERY() 将对象和数组作为有效的 JSON 字符串返回，可以存储、传递给其他函数或返回到应用程序。

路径表达式使用$表示根元素，点表示法用于嵌套属性，括号表示法用于数组元素，具体如以下示例：

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

结果为：

FirstProduct
------------
Widget

数组索引从 0 开始，因此 $.items[0] 指第一个元素。在你知道位置时，使用此语法来提取特定项；当需要处理所有数组元素时，将其与 OPENJSON 结合使用。

小窍门

当需要标量值进行比较或计算时使用 JSON_VALUE() 。需要保留嵌套对象或数组的 JSON 结构时使用 JSON_QUERY() 。

使用 `OPENJSON` 分析 JSON 数组

OPENJSON 是一个表值函数，用于将 JSON 数据转换为关系行集。使用此函数将 JSON 数据与关系表联接，或单独处理数组元素。

以下查询将 JSON 数组分析为具有默认架构的行：

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

结果集将为：

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

如果没有架构， OPENJSON 则返回三列： key （数组索引或属性名称）、 value （JSON 内容）和 type （指示 JSON 数据类型的数字：0=null、1=string、2=number、3=boolean、4=array、5=object）。

以下查询定义了一个显式架构，用于提取具有适当数据类型的特定列：

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

结果集将为：

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

WITH 子句将 JSON 属性映射到类型化列。此方法提供用于计算和比较的适当数据类型，并允许仅选择所需的属性。

将OPENJSON与表格数据结合使用CROSS APPLY：

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

注释

使用OPENJSONCROSS APPLY时，包含NULL或空 JSON 值的主表中的行不会出现在结果中。如果需要包含不包含 JSON 数据的行，请使用 OUTER APPLY 。

使用 `JSON_OBJECT` 和 `JSON_ARRAY` 构造 JSON

SQL Server 2022 引入了 JSON_OBJECT 和 JSON_ARRAY 函数，用于直观地构造 JSON：

JSON_OBJECT() 从键值对创建 JSON 对象，以下示例演示如何为产品生成 JSON 对象：

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

结果为：

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

该函数自动处理数据类型转换，并针对字符串值中的特殊字符进行适当的 JSON 转义。

JSON_ARRAY() 根据值创建 JSON 数组，以下示例生成 JSON 数组：

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

结果为：

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

可以将列值、变量或文本值传递给 JSON_ARRAY()。无论输入类型如何，该函数都会创建格式正确的 JSON 数组。

然后，合并这些函数以生成嵌套 JSON 结构。以下示例使用客户和总计信息构造完整的订单 JSON 对象：

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

结果为：

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

嵌套 JSON_OBJECT 调用将创建与应用程序预期格式匹配的分层结构。此方法比字符串串联更简洁，可确保有效的 JSON 输出。

使用`JSON_ARRAYAGG`汇总数据

JSON_ARRAYAGG 将多行中的值聚合到单个 JSON 数组中。此函数可用于从规范化关系数据创建非规范化 JSON 输出：

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

结果为：

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

该函数收集分组行中的所有匹配值，并将其合并为单个 JSON 数组。这对于从规范化数据库表创建非规范化 API 响应非常有用。

可以将JSON_ARRAYAGG和JSON_OBJECT结合起来创建复杂对象的数组：

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

以下结果为：

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

重要

JSON_ARRAYAGG 和 JSON_OBJECT/JSON_ARRAY 函数在 SQL Server 2022 及更高版本、Azure SQL 数据库以及 Microsoft Fabric 的 SQL 数据库中均可用。对于早期版本，请使用 FOR JSON PATH 来实现类似功能。

使用 `JSON_CONTAINS` 验证和检查 JSON

来自外部源的 JSON 数据的格式不正确、缺少预期属性或包含意外值。尝试从无效的 JSON 或缺失路径中提取值可能会导致查询失败或返回屏蔽数据问题的误导 NULL 性结果。

可靠的 JSON 处理需要防御性编码：在分析 JSON 之前验证 JSON 的格式是否良好，在提取值之前检查预期路径是否存在，并在业务逻辑中使用它们之前验证值是否与预期匹配。 SQL Server 提供了多个函数来帮助验证每个处理阶段的 JSON 内容。

了解宽松与严格路径模式

可以在控制错误处理的两种模式下使用 JSON 路径表达式：

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

结果为：

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

缺少属性时使用 lax 模式（默认值），应返回 NULL。当缺少属性表明存在应引发错误的数据问题时，使用 strict 模式。

ISJSON 验证字符串是否包含有效的 JSON。下面的示例演示如何使用 ISJSON：

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

结果为：

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

在 WHERE 子句中使用 ISJSON 筛选包含有效 JSON 的行，或在 CASE 表达式中优雅地处理无效数据。

JSON_PATH_EXISTS 检查 JSON 文档中是否存在特定路径，如以下示例所示：

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

结果为：

HasName   HasEmail
-------   --------
1         0

如果路径存在，则此函数返回 1;如果路径不存在，则返回 0。在严格模式下调用 JSON_VALUE 之前使用它，或有条件地处理结构各异的 JSON。

用于 JSON_CONTAINS 检查 JSON 文档是否包含特定值或对象，如以下示例所示：

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

结果为：

HasSqlTag   HasPythonTag
---------   ------------
1           0

使用计算列优化 JSON 查询

经常查询特定的 JSON 属性时，数据库引擎必须分析每个查询上每一行的 JSON 文档。对于包含数千行或数百万行的表，这种重复分析会产生巨大的开销。计算列允许提取 JSON 值一次，并将其存储为支持索引的可查询格式。

为什么 JSON 分析会影响性能

考虑一个包含 100,000 条产品记录的表，其中每行包含具有产品属性的 JSON 文档。按类别筛选的查询必须：

从表中读取每行
分析 JSON 文档以查找类别属性
提取和比较数值

如果不进行优化，即使是简单的筛选器也需要进行完整的表扫描，并对每行进行 JSON 解析。

为 JSON 属性创建计算列

计算列会自动提取 JSON 属性并使其作为常规列提供，如以下示例所示：

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

结果为：

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

默认情况下，计算列是虚拟的。数据库在查询时计算值，但可以优化 JSON 提取。为了获得更好的性能，可以保留计算列，如以下示例所示：

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

持久化列将提取的值存储在磁盘上，因此 JSON 仅在 INSERT 和 UPDATE 操作期间解析，而不是在 SELECT 查询期间解析。

添加索引以加快筛选速度

实际性能提升来自对计算列编制索引：

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

如果没有索引，查询将扫描所有 100,000 行。使用索引时，查询引擎执行索引查找并仅检索匹配的行。这可将查询时间从秒缩短到毫秒。

为多个 JSON 属性编制索引

对于筛选多个 JSON 属性的查询，请创建计算列和复合索引：

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

现在，按类别和品牌筛选或按价格排序的查询可以有效地使用这些索引。

小窍门

对于经常访问的 JSON 属性，与在查询时分析 JSON 相比，具有索引的计算列可以提高查询性能。监视查询模式，并为在WHERE、JOIN或ORDER BY子句中使用的属性创建计算列。

使用 FOR JSON 将关系数据转换为 JSON

有关查询的综合 JSON 输出，请使用 FOR JSON PATH 或 FOR JSON AUTO：

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

结果为：

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH 通过列别名控制 JSON 结构。在别名中使用点表示法创建嵌套对象：

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

结果为：

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

列别名 'product.id' 创建了一个具有 id 属性的嵌套 product 对象。使用此方法可以调整输出，使其与 API 的预期格式匹配，而无需后期处理。

有关 SQL Server 中的 JSON 函数的详细信息，请参阅 SQL Server 和 JSON Functions 中的 JSON 数据。

反馈

此页面是否有帮助？

使用内置函数处理 JSON 数据

使用JSON_VALUE和JSON_QUERY提取值

使用 OPENJSON 分析 JSON 数组

使用 JSON_OBJECT 和 JSON_ARRAY 构造 JSON

使用JSON_ARRAYAGG汇总数据

使用 JSON_CONTAINS 验证和检查 JSON

了解宽松与严格路径模式

使用计算列优化 JSON 查询

为什么 JSON 分析会影响性能

为 JSON 属性创建计算列

添加索引以加快筛选速度

为多个 JSON 属性编制索引

使用 FOR JSON 将关系数据转换为 JSON

反馈

使用 `OPENJSON` 分析 JSON 数组

使用 `JSON_OBJECT` 和 `JSON_ARRAY` 构造 JSON

使用`JSON_ARRAYAGG`汇总数据

使用 `JSON_CONTAINS` 验证和检查 JSON