在数据库里折腾 JSON 数组更新，这事儿看着简单，实际坑不少。先说说核心体会：不同数据库的解决方案各有门道，但本质都是先定位、再替换，没有一条万能的 SQL 语句。下面逐个拆解，顺便把那些容易踩的坑一一指出来。
举个具体场景：你有个表存了用户 JSON 字段，里面带一个 tags 数组，想把其中 id=202 那个标签的 label 改成 "lead"。这在 MySQL 5.7+ 里可没那么直给——它不支持用条件直接匹配数组元素，得绕个弯子。
MySQL 5.7+ 中用 JSON_SET 修改数组内某个对象的字段
MySQL 原生不支持直接通过下标或条件定位 JSON 数组中的某一项并更新，必须先定位到目标元素（靠路径），再用 JSON_SET 替换。比如有个表 users，其中 profile 字段存着 JSON：
{"name": "Alice", "tags": [{"id": 101, "label": "dev"}, {"id": 202, "label": "mgr"}]}
想把 id=202 的 label 改成 "lead"，不能写 WHERE tags[*].id = 202 —— 这种语法 MySQL 不认。
实际做法是：先用 JSON_SEARCH 找出匹配项的路径，再拼接路径传给 JSON_SET。但注意：JSON_SEARCH 返回的是带引号的字符串（如 "$[1].label"），而 JSON_SET 要的是无引号的路径表达式，所以得手动构造路径或用变量中转。
- 最稳妥的方式是用子查询 +
JSON_EXTRACT遍历数组（配合JSON_LENGTH和循环变量），但 MySQL 不支持原生循环，只能靠应用层或存储过程 - 简单场景可硬编码索引：若确定目标总在第 2 项，直接用
JSON_SET(profile, '$.tags[1].label', 'lead') JSON_REPLACE和JSON_SET区别在于：前者只替换已存在的键，后者会新增键；对数组元素更新，两者行为一致，但建议统一用JSON_SET
PostgreSQL 中用 jsonb_set + jsonb_array_elements 定位更新
PostgreSQL 的 jsonb 类型更灵活，能结合集合操作定位数组元素。核心思路是：把数组展开为行，筛选出目标项，拿到其序号（ordinality），再用该序号构造路径更新原 JSON。
例如表 users 的 data 是 jsonb，含 "roles": [{"id": 101, "name": "user"}, {"id": 202, "name": "admin"}]，要改 id=202 的 name：
UPDATE users
SET data = jsonb_set(
data,
ARRAY['roles', (idx-1)::text, 'name'],
'"lead"',
true
)
FROM (
SELECT id AS user_id, idx
FROM users u,
jsonb_array_elements(u.data->'roles') WITH ORDINALITY AS r(role, idx)
WHERE (role->>'id')::int = 202
) s
WHERE users.id = s.user_id;
关键点：
WITH ORDINALITY给数组元素编号，从 1 开始，但 JSON 路径下标从 0，所以要(idx-1)::textjsonb_set第三个参数必须是合法 JSON 字符串（如'"lead"'或用to_jsonb('lead')）- 没匹配到时子查询为空，UPDATE 不生效，安全
SQLite 3.38+ 用 json_replace 和 json_each 模拟条件更新
SQLite 原生不支持按值查找 JSON 数组索引，但 3.38 引入了 json_each 表函数，可以遍历数组并返回 key（即下标）。配合 CASE 和 json_replace 可实现单次更新。
假设表 config 有字段 settings（JSON），含 {"items": [{"k": "a", "v": 1}, {"k": "b", "v": 2}]}，要把 k="b" 的 v 改成 99：
UPDATE config
SET settings = json_replace(
settings,
'$.items[' || (
SELECT key FROM json_each(settings, '$.items')
WHERE value LIKE '%"k":"b"%'
) || '].v',
99
);
注意：
json_each的value是子 JSON 对象，用LIKE匹配字符串容易出错（比如"k":"ab"也会命中），更可靠的做法是用json_extract(value, '$.k')，但 SQLite 不允许在子查询里嵌套json_extract两次，所以常退而求其次用字符串匹配- 如果数组里没有匹配项，
SELECT key返回 NULL，整个json_replace会失败（报错或不动），需加WHERE EXISTS(...)判断 - 路径拼接必须用
||，且下标必须是字符串，不能是数字
通用避坑点：JSON 路径语法差异和性能隐患
不同数据库对 JSON 路径的支持程度差异很大：$[0].name 在 MySQL/PostgreSQL/SQLite 都可用，但 $[?(@.id==202)]（JSONPath）只有 PostgreSQL 的 jsonpath 类型支持，MySQL 完全不认，SQLite 也不支持。
性能上，所有方案本质都是“全量解析 + 遍历 + 重建 JSON”，数据量大时（比如数组上千项）会明显变慢。真正高频更新 JSON 数组内元素的场景，应该考虑反规范化：把数组拆成独立表（如 user_tags(user_id, tag_id, label)），用标准 SQL 更新，而不是硬扛 JSON 操作。
另外，MySQL 的 JSON_VALID 和 PostgreSQL 的 jsonb 类型约束容易被忽略——如果 UPDATE 后 JSON 格式出错（比如少了个逗号），MySQL 会静默截断或报错，PostgreSQL 则直接拒绝，SQLite 更是完全不校验。上线前务必用真实数据验证更新结果是否符合预期。
