如何为SQL表中JSON列强制指定特定JSON结构？

当然可以在数据库端实现JSON结构校验，不用完全依赖客户端或后端。下面是针对你给出的JSON结构的具体实现方式：

1. 用CHECK约束结合JSON函数做基础校验 #

直接通过SQL内置的JSON函数，检查必填字段是否存在、类型是否匹配，把校验逻辑绑定到表的CHECK约束中：

示例约束语句 #

ALTER TABLE YourTableName
ADD CONSTRAINT CK_ValidJSONStructure
CHECK (
    -- 校验根节点的name、description为非空字符串
    JSON_VALUE(MY_JSON_COLUMN, '$.name') IS NOT NULL
    AND JSON_VALUE(MY_JSON_COLUMN, '$.description') IS NOT NULL
    -- 校验friends是合法数组
    AND JSON_QUERY(MY_JSON_COLUMN, '$.friends') IS NOT NULL
    -- 校验friends数组中每个元素都包含name和description字段
    AND (SELECT COUNT(*)
         FROM OPENJSON(MY_JSON_COLUMN, '$.friends')
         WITH (
             name NVARCHAR(MAX) '$.name',
             description NVARCHAR(MAX) '$.description'
         )
         WHERE name IS NULL OR description IS NULL) = 0
)

插入或更新数据时，这个约束会自动生效：只要JSON不符合要求，操作就会被数据库拦截。

2. 用JSON Schema做严格校验（适用于SQL Server 2019+） #

如果你的数据库版本支持，可以用标准JSON Schema定义结构规则，再通过JSON_SCHEMA_VALIDATION函数做校验，这种方式更适合复杂结构：

步骤1：定义目标结构的JSON Schema #

{
  "type": "object",
  "required": ["name", "description", "friends"],
  "properties": {
    "name": {"type": "string"},
    "description": {"type": "string"},
    "friends": {
      "type": "array",
      "items": {
        "type": "object",
        "required": ["name", "description"],
        "properties": {
          "name": {"type": "string"},
          "description": {"type": "string"}
        }
      }
    }
  }
}

步骤2：绑定到CHECK约束 #

ALTER TABLE YourTableName
ADD CONSTRAINT CK_ValidJSONSchema
CHECK (
    JSON_SCHEMA_VALIDATION(N'{
      "type": "object",
      "required": ["name", "description", "friends"],
      "properties": {
        "name": {"type": "string"},
        "description": {"type": "string"},
        "friends": {
          "type": "array",
          "items": {
            "type": "object",
            "required": ["name", "description"],
            "properties": {
              "name": {"type": "string"},
              "description": {"type": "string"}
            }
          }
        }
      }
    }', MY_JSON_COLUMN) = 1
)

这种方式能严格校验字段类型、必填项、数组结构，比单独用JSON函数更规范。

3. 自定义函数封装校验逻辑（兼容低版本数据库） #

如果你的数据库版本不支持JSON Schema，可以写一个自定义函数封装校验逻辑，再绑定到CHECK约束：

示例自定义函数 #

CREATE FUNCTION dbo.ValidateJSONStructure(@json NVARCHAR(MAX))
RETURNS BIT
AS
BEGIN
    -- 先校验是否为合法JSON
    IF @json IS NULL OR ISJSON(@json) = 0
        RETURN 0;

    -- 校验根节点必填字段
    IF JSON_VALUE(@json, '$.name') IS NULL OR JSON_VALUE(@json, '$.description') IS NULL
        RETURN 0;

    -- 校验friends数组及内部元素
    DECLARE @invalidFriends INT;
    SELECT @invalidFriends = COUNT(*)
    FROM OPENJSON(@json, '$.friends')
    WITH (
        name NVARCHAR(MAX) '$.name',
        description NVARCHAR(MAX) '$.description'
    )
    WHERE name IS NULL OR description IS NULL;

    IF @invalidFriends > 0 OR JSON_QUERY(@json, '$.friends') IS NULL
        RETURN 0;

    RETURN 1;
END

绑定到CHECK约束 #

ALTER TABLE YourTableName
ADD CONSTRAINT CK_ValidJSONUsingFunction
CHECK (dbo.ValidateJSONStructure(MY_JSON_COLUMN) = 1)

补充说明 #

• 数据库端校验是最后一道防线，建议客户端/后端也做前置校验，减少无效请求和数据库压力
• 以上方案以SQL Server为例，其他数据库（如PostgreSQL的jsonb类型、MySQL的JSON函数）也有类似实现逻辑，核心思路一致

内容的提问来源于stack exchange，提问作者henhen