在业务系统运行过程中，常需要将结构灵活的JSON数据存储到SQL数据库的字段中，但上游系统传入的JSON数据往往存在格式错误、字段缺失、值类型不符等问题，直接存储会影响后续业务查询和数据分析的准确性，因此需要在入库前或查询时完成JSON数据的校验与清洗工作。
JSON数据常见问题类型
实际场景中JSON数据的问题主要分为以下几类：
- 格式不合法：不符合JSON语法规范，比如缺少引号、括号不匹配、存在多余逗号等
- 字段缺失：必填字段不存在，或者可选字段缺失但后续逻辑需要用到默认值
- 值类型异常：字段值的类型不符合预期，比如本应是数字的字段存了字符串，本应是数组的字段存了单个值
- 值内容异常：字段值超出合理范围，比如年龄字段出现负数、日期格式不符合标准等
SQL中的JSON校验实现
不同数据库对JSON的支持程度不同，这里以MySQL 8.0为例，介绍校验逻辑的实现方式。首先可以通过JSON_VALID()函数判断JSON字符串是否合法：
-- 判断JSON字段是否合法
SELECT
id,
json_data,
JSON_VALID(json_data) AS is_valid
FROM test_json_table;
如果JSON_VALID()返回1表示格式合法，返回0表示不合法。对于不合法的JSON数据，可以先标记为异常数据，后续单独处理。接下来校验必填字段是否存在，使用JSON_CONTAINS_PATH()函数：
-- 校验必填字段name和age是否存在
SELECT
id,
json_data,
JSON_CONTAINS_PATH(json_data, 'all', '$.name', '$.age') AS has_required_fields
FROM test_json_table
WHERE JSON_VALID(json_data) = 1;
上述语句中'all'表示所有指定路径的字段都必须存在，返回1则符合要求。如果要校验字段值的类型，可以结合JSON_TYPE()函数判断：
-- 校验name字段为字符串类型，age字段为数字类型
SELECT
id,
json_data,
CASE
WHEN JSON_TYPE(JSON_EXTRACT(json_data, '$.name')) = 'STRING' THEN 1
ELSE 0
END AS name_is_string,
CASE
WHEN JSON_TYPE(JSON_EXTRACT(json_data, '$.age')) = 'INTEGER'
OR JSON_TYPE(JSON_EXTRACT(json_data, '$.age')) = 'DECIMAL'
THEN 1
ELSE 0
END AS age_is_number
FROM test_json_table
WHERE JSON_VALID(json_data) = 1
AND JSON_CONTAINS_PATH(json_data, 'all', '$.name', '$.age') = 1;
JSON数据清洗实践
校验完成后，针对异常数据可以进行清洗处理，常见的清洗逻辑包括补全缺失字段、转换值类型、过滤异常值等。
补全缺失字段
对于可选字段缺失的情况，可以设置默认值，使用JSON_SET()函数实现：
-- 补全缺失的gender字段，默认值为未知 UPDATE test_json_table SET json_data = JSON_SET(json_data, '$.gender', '未知') WHERE JSON_VALID(json_data) = 1 AND JSON_CONTAINS_PATH(json_data, 'one', '$.gender') = 0;
转换值类型
如果age字段存的是字符串类型的数字，需要转换为数字类型，可以先提取字符串再转换：
-- 将字符串类型的age转换为数字类型
UPDATE test_json_table
SET json_data = JSON_SET(
json_data,
'$.age',
CAST(JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.age')) AS UNSIGNED)
)
WHERE JSON_VALID(json_data) = 1
AND JSON_CONTAINS_PATH(json_data, 'all', '$.age') = 1
AND JSON_TYPE(JSON_EXTRACT(json_data, '$.age')) = 'STRING';
过滤异常值
对于年龄小于0或者大于150的异常数据，可以直接修正为合理值，或者标记为无效数据：
-- 修正异常的年龄值，小于0设为0，大于150设为150
UPDATE test_json_table
SET json_data = JSON_SET(
json_data,
'$.age',
CASE
WHEN JSON_EXTRACT(json_data, '$.age') < 0 THEN 0
WHEN JSON_EXTRACT(json_data, '$.age') > 150 THEN 150
ELSE JSON_EXTRACT(json_data, '$.age')
END
)
WHERE JSON_VALID(json_data) = 1
AND JSON_CONTAINS_PATH(json_data, 'all', '$.age') = 1
AND JSON_TYPE(JSON_EXTRACT(json_data, '$.age')) IN ('INTEGER', 'DECIMAL');
完整校验清洗流程示例
可以将校验和清洗逻辑整合成一个存储过程，批量处理表中的JSON数据：
DELIMITER //
CREATE PROCEDURE clean_json_data()
BEGIN
-- 步骤1：标记格式不合法的JSON数据
UPDATE test_json_table
SET status = 'invalid_format'
WHERE JSON_VALID(json_data) = 0;
-- 步骤2：补全必填字段缺失的数据
UPDATE test_json_table
SET json_data = JSON_SET(json_data, '$.create_time', NOW())
WHERE status IS NULL
AND JSON_CONTAINS_PATH(json_data, 'one', '$.create_time') = 0;
-- 步骤3：修正异常的年龄值
UPDATE test_json_table
SET json_data = JSON_SET(
json_data,
'$.age',
CASE
WHEN JSON_EXTRACT(json_data, '$.age') < 0 THEN 0
WHEN JSON_EXTRACT(json_data, '$.age') > 150 THEN 150
ELSE JSON_EXTRACT(json_data, '$.age')
END
)
WHERE status IS NULL
AND JSON_CONTAINS_PATH(json_data, 'all', '$.age') = 1;
-- 步骤4：标记清洗完成的数据
UPDATE test_json_table
SET status = 'cleaned'
WHERE status IS NULL;
END //
DELIMITER ;
-- 调用存储过程执行清洗
CALL clean_json_data();
注意事项
不同数据库的JSON函数存在差异，比如PostgreSQL使用jsonb_valid()校验JSON合法性，SQL Server使用ISJSON()函数，需要根据实际使用的数据库调整函数。另外清洗操作会修改原始数据，建议先备份数据或者在测试环境验证后再执行生产环境操作。