SQLSERVER：JSON数据的读写

技术老小子 2024-02-03

JSON (JavaScript Object Notation) 是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。自 SQL Server 2016 起，SQL Server 提供了对 JSON 数据的内置支持，允许用户解析、查询、存储和输出 JSON 数据。以下是如何在 SQL Server 中处理 JSON 数据的详细指南。

实例数据表

假设我们有一个名为 Customers
的表，其中包含 JSON 格式的地址数据：

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    AddressJSON NVARCHAR(MAX)
);


INSERT INTO Customers (CustomerID, Name, AddressJSON)
VALUES
(1, 'John Doe', '{"Street":"123 Main St", "City":"New York", "ZipCode":"10001"}'),
(2, 'Jane Smith', '{"Street":"456 Center Rd", "City":"Los Angeles", "ZipCode":"90001"}'),
(3, 'Mike Johnson', '{"Street":"789 Side Ave", "City":"Chicago", "ZipCode":"60601"}');

查询 JSON 数据

您可以使用 JSON_VALUE
函数来提取 JSON 字符串中的特定值：

SELECT
    CustomerID,
    Name,
    JSON_VALUE(AddressJSON, '$.City') AS City
FROM Customers;

这个查询将返回每个顾客的城市信息。

修改 JSON 数据

可以通过将整个 JSON 字符串替换为更新后的版本来修改 JSON 数据。目前 SQL Server 不支持直接修改 JSON 字符串中的单个属性。

UPDATE Customers
SET AddressJSON = JSON_MODIFY(AddressJSON, '$.ZipCode', '10002')
WHERE CustomerID = 1;

这个脚本将为 CustomerID 为 1 的顾客更新邮政编码。

将 JSON 转换为关系数据

使用 OPENJSON
函数将 JSON 数据转换为行集合：

SELECT
    CustomerID,
    Name,
    JSONData.*
FROM
    Customers
CROSS APPLY
    OPENJSON(AddressJSON)
    WITH (
        Street NVARCHAR(50) '$.Street',
        City NVARCHAR(50) '$.City',
        ZipCode NVARCHAR(10) '$.ZipCode'
    ) AS JSONData;

这个查询将返回一个包含街道、城市和邮政编码的扁平化数据表。

将关系数据转换为 JSON

使用 FOR JSON
子句将关系数据转换为 JSON 格式：

SELECT
    CustomerID,
    Name,
    AddressJSON
FROM Customers
FOR JSON PATH, ROOT('Customers');

这将生成一个包含所有顾客信息的 JSON 文档。

使用 JSON 查询

JSON_QUERY
函数用于提取 JSON 对象或数组，而非单个标量值：

SELECT
    CustomerID,
    Name,
    JSON_QUERY(AddressJSON, '$') AS Address
FROM Customers
WHERE JSON_VALUE(AddressJSON, '$.City') = 'New York';

这将返回所有在纽约市的顾客及其地址。

验证 JSON 数据

使用 ISJSON
函数验证字符串是否包含有效的 JSON 数据：

SELECT
    CustomerID,
    Name,
    AddressJSON,
    ISJSON(AddressJSON) AS IsValidJSON
FROM Customers;

这将返回每个顾客的地址数据及其是否为有效 JSON 的指示。

总结

SQL Server 中的 JSON 功能提供了与 JSON 数据进行互动的便捷方法。从简单的提取值到复杂的 JSON 数据转换，SQL Server 都能够处理各种 JSON 相关的任务。通过上述示例，开发人员可以更好地理解如何在 SQL Server 中利用 JSON 功能，并使用相关的方法和函数来执行各种操作。

请注意，与 XML 功能类似，JSON 数据处理在 SQL Server 中可能会有性能影响，特别是在处理大量数据时。因此，开发人员在设计和实现时应考虑性能最佳实践，如避免复杂的 JSON 查询以及在可能的情况下使用关系数据代替 JSON 数据。

文章转载自技术老小子，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。