管理 JSON 列和索引

8 分钟

当表中的每一行具有相同列时，关系数据库效果最佳。定义一次结构，每个记录都遵循该结构。此设计适用于字段可预测的情况下，如客户、订单或发票等数据。但某些数据因记录而异。需要存储的属性取决于项的类型、数据源或用户所做的选择。传统的表格设计迫使你要么创建许多对大多数行都为空的列，要么将数据分散到多个表。 JSON 列提供另一个选项：将变量部分存储为 JSON，同时将可预测部分保存在常规列中。

例如，电子商务产品目录具有适用于每个项目的公用字段，例如产品名称、价格和类别。但衬衫需要大小和颜色，笔记本电脑需要处理器速度和屏幕大小，书籍需要作者和其他属性。使用 JSON，可将公共字段存储为列，并将特定于类别的属性放入 JSON 列中。无需更改表结构即可添加新的产品类型。

了解何时使用 JSON 列

JSON 列允许使用熟悉的 SQL 语法查询和索引半结构化数据。无需单独的 NoSQL 数据库来处理灵活的数据。对于以下方案，请考虑 JSON：

用户首选项 - 添加功能时，主题、语言和通知选项等设置因用户而异。
API 响应 - 来自外部服务的数据具有可在提供程序更新其 API 时更改的嵌套结构。
审核日志 - 状态前后捕获的记录需要随着表架构的发展而进行调整。
多租户应用程序 - 不同的客户需要不同的自定义字段。
灵活的元数据 - 标记、标签和属性，这些标记和属性因记录而异，并且不适合固定架构。

创建和查询 JSON 列

SQL Server 2025 引入了本机 json 数据类型，该数据类型以优化的二进制格式存储 JSON 文档，从而方便地进行查询与操作。本机类型在读取时更高效（因为文档已被解析），在写入时也更高效（更新能够在不重写整个文档的情况下修改单个值），并且与将 JSON 作为NVARCHAR(MAX)存储相比，具有更好的存储压缩效果。

对于早期版本的 SQL Server，将 JSON 存储在 NVARCHAR(MAX) 列中。

若要从 JSON 读取值，请使用 JSON 函数，例如 JSON_VALUE 提取单个值或 JSON_QUERY 返回对象或数组。如果经常查询 JSON 属性，可以在提取该属性的计算列上创建索引。

以下示例创建包含 JSON 列的表、插入文档、查询特定属性、更新值，并在经常访问的字段上创建索引：

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

此示例创建一个包含 JSON 存储用户配置设置的列的表。这些 INSERT 语句将 JSON 文档添加为字符串文本。若要读取特定值，JSON_VALUE 提取主题和语言等标量值，同时 JSON_QUERY 返回整个 JSON 对象。该方法 .modify() （当前以预览版提供）更新单个属性，而无需重写整个文档。 json由于该类型不能用作索引键列，因此该示例将创建一个计算列来提取主题值，然后为该计算列编制索引。

合并关系和 JSON 结构

JSON 列最适合每条记录数据结构各异的情况。如果每行具有相同的数据类型的字段，则常规列更适合。可以获取原生数据类型验证、无需 JSON 路径语法的更简单查询，并直接对列进行索引。对需要灵活性的数据部分使用 JSON，并将可预测部分保留在类型化列中。

对于需要变量元数据的产品，可以将关系结构与 JSON 灵活性相结合。下面是一个示例：

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;

考虑 JSON 设计原则

实现 JSON 列时应用以下原则：

将 JSON 用于半结构化数据 - 存储灵活的数据结构，这些结构因记录而异，而不是具有一致的架构的数据。
索引经常查询的路径 - 创建计算列，其中包含经常查询的 JSON 属性上的索引。
验证所需的属性 - 使用 CHECK 约束 JSON_PATH_EXISTS 来确保必填字段存在。
平衡灵活性与结构 - 在常规列中保留可预测的数据，并仅对变量部分使用 JSON。

JSON 列在保持 SQL 查询功能的同时，为可变数据提供了架构灵活性，但应作为结构化数据关系设计的补充而非替代。

反馈

此页面是否有帮助？