akmon/JSON_SYNTAX_FIX.md

JSON 语法错误修复

问题描述

在执行模拟数据插入时遇到 PostgreSQL JSON 语法错误：

ERROR: 22P02: invalid input syntax for type json
LINE 252: WHEN 'share' THEN '{"platform": "weibo", "shared_at": "' || NOW() || '"}'::jsonb
DETAIL: Token ""}" is invalid.
CONTEXT: JSON data, line 1: "}

问题根源

错误发生在用户交互数据插入的 CASE 语句中，尝试通过字符串拼接来构建包含时间戳的 JSON：

问题代码

CASE interaction_type
    WHEN 'share' THEN '{"platform": "weibo", "shared_at": "' || NOW() || '"}'::jsonb
    WHEN 'favorite' THEN '{"folder": "我的收藏", "added_at": "' || NOW() || '"}'::jsonb
    ELSE '{}'::jsonb
END

问题分析

1. 时间戳格式问题: NOW() 返回的时间戳格式可能包含特殊字符
2. JSON 转义问题: 直接字符串拼接没有正确处理 JSON 转义
3. 语法错误: 生成的字符串不是有效的 JSON 格式

解决方案

使用 PostgreSQL 的 jsonb_build_object() 函数来安全地构建 JSONB 对象：

修复后的代码

CASE interaction_type
    WHEN 'share' THEN jsonb_build_object('platform', 'weibo', 'shared_at', NOW())
    WHEN 'favorite' THEN jsonb_build_object('folder', '我的收藏', 'added_at', NOW())
    ELSE '{}'::jsonb
END

技术优势

使用 jsonb_build_object() 的优势：

1. 自动转义: 函数会自动处理 JSON 中的特殊字符和转义
2. 类型安全: 正确处理不同数据类型（字符串、时间戳等）
3. 语法保证: 生成的始终是有效的 JSONB 格式
4. 性能优化: 原生函数比字符串拼接更高效

修复前后对比

修复前（❌ 错误）：

-- 字符串拼接方式，容易出错
'{"platform": "weibo", "shared_at": "' || NOW() || '"}'::jsonb

修复后（✅ 正确）：

-- 使用专用函数，安全可靠
jsonb_build_object('platform', 'weibo', 'shared_at', NOW())

生成的数据示例

修复后，interaction_data 字段将包含正确格式的 JSONB 数据：

分享数据：

{
  "platform": "weibo",
  "shared_at": "2025-07-07T10:30:45.123Z"
}

收藏数据：

{
  "folder": "我的收藏",
  "added_at": "2025-07-07T10:30:45.123Z"
}

PostgreSQL JSONB 函数说明

jsonb_build_object()

• 语法: jsonb_build_object(key1, value1, key2, value2, ...)
• 功能: 从键值对列表构建 JSONB 对象
• 优势: 自动处理类型转换和 JSON 转义

其他相关函数

• jsonb_build_array(): 构建 JSONB 数组
• to_jsonb(): 将任意值转换为 JSONB
• jsonb_set(): 设置 JSONB 对象中的值

最佳实践

1. 避免字符串拼接: 不要用 || 操作符拼接 JSON 字符串
2. 使用原生函数: 优先使用 PostgreSQL 的 JSONB 函数
3. 处理特殊字符: 让数据库自动处理转义和特殊字符
4. 类型安全: 让函数处理不同数据类型的转换

验证结果

• ✅ SQL 语法检查: 通过 PostgreSQL 语法验证
• ✅ JSON 格式: 生成有效的 JSONB 数据
• ✅ 时间戳处理: 正确处理 NOW() 函数返回值
• ✅ 中文支持: 正确处理中文字符

相关资源

• PostgreSQL JSONB 函数文档
• JSONB vs JSON 性能对比
• JSON 数据类型最佳实践

文件状态

• 文件: h:\blews\akmon\video_system_database.sql
• 修复行数: 第 990-995 行
• 状态: ✅ 已修复，JSON 语法正确
• 更新时间: 2025-07-07

---

JSON 字符串拼接问题已成功修复，现在使用 PostgreSQL 原生 JSONB 函数来安全构建 JSON 数据。