JSONB 的增删改操作符

JSONB 是不可变值——每次"修改"实际上是用新值替换旧值。掌握 jsonb_set、||、-、#- 等操作符，能在不重写整个 JSON 对象的情况下精确更新嵌套字段，避免应用层"读取-反序列化-修改-序列化-写回"的低效模式。

操作符速查表

|| 合并操作符

-- 最简单的"添加/更新键"方式：合并
-- 规则：右边的键值对覆盖左边的同名键，不同名的键保留
SELECT
'{"color": "Silver", "memory": "16GB"}'::jsonb
|| '{"color": "Space Gray", "storage": "512GB"}'::jsonb;
-- 结果：{"color": "Space Gray", "memory": "16GB", "storage": "512GB"}
-- color 被覆盖，memory 保留，storage 新增
-- 实际 UPDATE 使用
UPDATE products
SET attributes = attributes || '{"in_stock": true, "last_updated": "2024-03-22"}'
WHERE id = 1;
-- 批量给所有产品添加默认字段
UPDATE products
SET attributes = '{"currency": "CNY"}'::jsonb || attributes
-- 注意顺序：左边是默认值，右边的 attributes 会覆盖同名键
-- 这样只给没有 currency 字段的产品添加默认值
WHERE attributes ->> 'currency' IS NULL;

jsonb_set：精确修改指定路径

-- jsonb_set(target, path, new_value, create_missing)
-- target: 原始 JSONB
-- path: 文本数组，指定路径
-- new_value: 新的 JSONB 值（注意：字符串要加引号才是合法 JSON）
-- create_missing: 路径不存在时是否创建（默认 true）
-- 修改顶层键
UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"Space Gray"')
WHERE id = 1;
-- 修改嵌套键
-- 假设 attributes = {"specs": {"cpu": "M3", "ram": "16GB"}}
UPDATE products
SET attributes = jsonb_set(attributes, '{specs, ram}', '"32GB"')
WHERE id = 1;
-- 添加新键（路径不存在时自动创建）
UPDATE products
SET attributes = jsonb_set(attributes, '{warranty_years}', '2', true)
WHERE id = 1;
-- 修改数组元素（索引从 0 开始）
-- attributes = {"tags": ["laptop", "sale", "new"]}
UPDATE products
SET attributes = jsonb_set(attributes, '{tags, 1}', '"clearance"')
WHERE id = 1;
-- 结果：{"tags": ["laptop", "clearance", "new"]}
-- 常见错误：忘记字符串值要加 JSON 引号
-- ❌ 错误：
UPDATE products SET attributes = jsonb_set(attributes, '{color}', 'Space Gray');
-- 报错：invalid input syntax for type json
-- ✅ 正确：字符串值用双引号包裹，再用单引号作为 SQL 字符串
UPDATE products SET attributes = jsonb_set(attributes, '{color}', '"Space Gray"');
-- 或者用 to_jsonb 转换：
UPDATE products SET attributes = jsonb_set(attributes, '{color}', to_jsonb('Space Gray'::text));

- 删除键操作符

-- 删除顶层键
UPDATE products
SET attributes = attributes - 'weight'
WHERE id = 1;
-- 删除多个键（数组形式，PostgreSQL 10+）
UPDATE products
SET attributes = attributes - ARRAY['weight', 'old_sku', 'deprecated_flag']
WHERE id = 1;
-- 删除数组元素（按索引）
-- attributes = {"tags": ["laptop", "sale", "old"]}
UPDATE products
SET attributes = attributes - 2  -- 删除索引为 2 的元素（"old"）
WHERE id = 1;
-- 注意：- 数字 只用于 JSONB 数组，不能用于对象
-- 批量清理所有产品的废弃字段
UPDATE products
SET attributes = attributes - 'legacy_price'
WHERE attributes ? 'legacy_price';

#- 删除嵌套路径

-- 删除嵌套键（比 jsonb_set 设为 null 更彻底）
-- attributes = {"specs": {"cpu": "M3", "old_field": "deprecated"}}
UPDATE products
SET attributes = attributes #- '{specs, old_field}'
WHERE id = 1;
-- 结果：{"specs": {"cpu": "M3"}}
-- 删除数组中指定位置的元素
-- attributes = {"colors": ["red", "blue", "green"]}
UPDATE products
SET attributes = attributes #- '{colors, 1}'  -- 删除索引 1（"blue"）
WHERE id = 1;
-- 结果：{"colors": ["red", "green"]}

jsonb_insert：在数组中插入元素

-- jsonb_insert(target, path, new_value, insert_after)
-- insert_after: true=在路径后插入，false=在路径前插入（默认 false）
-- attributes = {"tags": ["laptop", "electronics"]}
-- 在数组开头插入
UPDATE products
SET attributes = jsonb_insert(attributes, '{tags, 0}', '"featured"')
WHERE id = 1;
-- 结果：{"tags": ["featured", "laptop", "electronics"]}
-- 在末尾插入（用不存在的大索引，或 {tags, -1} after）
UPDATE products
SET attributes = jsonb_insert(attributes, '{tags, -1}', '"new"', true)
WHERE id = 1;
-- insert_after=true 且 -1 表示最后一个元素之后
-- 结果：{"tags": ["featured", "laptop", "electronics", "new"]}

jsonb_strip_nulls：清理 null 值

-- 清除 JSONB 中所有 null 值的键（常用于 API 响应清理）
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}}'::jsonb);
-- 结果：{"a": 1, "c": {"e": 2}}
-- 实际应用：存储外部 API 响应时清除 null 字段
UPDATE webhook_events
SET payload = jsonb_strip_nulls(payload)
WHERE received_at > NOW() - INTERVAL '1 day';

复杂更新场景

条件性修改：只更新某个键（如果存在）

-- 只有当 attributes 中有 'price' 键时才打折
UPDATE products
SET attributes = jsonb_set(
attributes,
'{price}',
to_jsonb((attributes ->> 'price')::numeric * 0.9)
)
WHERE attributes ? 'price'
AND category = 'clearance';

原子计数器（JSONB 中的数值递增）

-- 把 JSONB 中的 view_count 加 1
UPDATE articles
SET metadata = jsonb_set(
metadata,
'{view_count}',
to_jsonb(COALESCE((metadata ->> 'view_count')::integer, 0) + 1)
)
WHERE id = 42;
-- 注意：这在高并发下会有竞争条件
-- 高频计数器建议用独立的整数列 + 关系型更新

将 JSONB 中的字段"提升"为关系列

-- 如果发现某个 JSONB 键被查询很频繁，把它升级为关系列
-- 步骤：
-- 1. 添加新列
ALTER TABLE products ADD COLUMN color TEXT;
-- 2. 从 JSONB 迁移数据
UPDATE products
SET color = attributes ->> 'color'
WHERE attributes ? 'color';
-- 3. 删除 JSONB 中的旧键（可选，视业务需要）
UPDATE products
SET attributes = attributes - 'color'
WHERE attributes ? 'color';
-- 4. 建立 B-tree 索引
CREATE INDEX idx_products_color ON products (color);

修改操作的性能考量

✅ 最高效的修改方式（减少序列化次数）：
- 用 || 一次性合并多个更新（比多次 jsonb_set 快）
- 批量操作用 WHERE IN 而不是循环单行更新
❌ 避免的模式：
- 在应用层读取 JSONB → 修改 → 写回（额外的序列化开销）
- 对频繁更新的字段用 JSONB（每次更新都写整行）
- 对需要 WHERE 过滤的 JSONB 字段不建索引

下一章：事务、锁与并发控制——PostgreSQL 的 MVCC 机制让读写不互相阻塞，但并发更新同一行时锁冲突仍然会发生。理解锁机制才能避免死锁和长时间阻塞。