JSONB 的更新与修改
JSONB 的更新是整个 JSON 章节里最容易「想当然」的一节：它没有字段级的原地修改，任何一次修改都是把整个值重新计算、重新写入。本节围绕三件事展开——替换（jsonb_set / jsonb_set_lax）、插入（jsonb_insert / ||）、删除（- / #-），并在最后讲清更新代价、行锁与并发丢更新这三个和线上稳定性直接相关的问题。
前置知识
阅读本节前，建议先了解：JSONB 的创建与插入、JSONB 的查询操作
基础概念
先纠正一个最常见的误解：
JSONB列里修改一个键，不是「改那个键」，而是「构造出一个新的 JSONB 值，覆盖整列」。
PostgreSQL 的 jsonb 是二进制规范化存储，内部按键排序、去重、多值共享（JsonbValue 结构 + 去重容器）。这套结构让读取极快，但代价是没有任何「就地修改一个键」的入口。所有更新函数做的事情都是：读入整个 jsonb → 在内存里构造新的树 → 序列化 → 写回整行。
因此更新的手段按「动作」分成三类：
| 动作 | 手段 | 适用对象 | 返回类型 |
|---|---|---|---|
| 替换 | jsonb_set、|（整体覆盖） | 对象键、数组下标 | jsonb |
| 插入 | jsonb_insert、|| | 数组元素、对象新键 | jsonb |
| 删除 | -、#- | 对象键、数组元素、路径 | jsonb |
| 清理 | jsonb_strip_nulls | 值为 null 的对象字段 | jsonb |
| 格式化 | jsonb_pretty | 只为展示 | text |
按「返回值来源」再分一层，这决定了你会不会踩 NULL 陷阱：
| 类别 | 代表 | 遇到路径/类型不匹配时的行为 |
|---|---|---|
| 操作符 | ||、-、#- | 大多宽容（部分场景报错） |
| 修改函数 | jsonb_set、jsonb_insert | 静默返回原值，或整体返回 NULL |
| 清理函数 | jsonb_strip_nulls | 递归处理，永不报错 |
关键术语：
create_if_missing（缺失即创建）：jsonb_set的可选布尔参数，默认true。为true时，路径最后一步不存在则新建；为false时只做替换，不存在就原样返回。insert_after（向后插入）：jsonb_insert的可选布尔参数，默认false（插在目标元素之前）。null_value_treatment（空值处置策略）：jsonb_set_lax的第四个参数，取值'raise_exception'、'use_json_null'（默认）、'delete_key'、'return_target'。- 整值重写（Whole-Value Rewrite）：
jsonb更新的底层实现方式，也是本节所有性能与并发结论的根源。 - TOAST：超大字段的行外存储机制，
jsonb超过约 2 KB 就会走 TOAST，更新时整个 TOAST 块都要重写。
语法与用法
-- 1. 替换：按路径替换，路径最后一步不存在时按 create_if_missing 决定是否新建
jsonb_set(target jsonb, path text[], new_value jsonb [, create_if_missing boolean]) → jsonb
-- 2. 替换（宽松版）：new_value 为 SQL NULL 时按 null_value_treatment 处置（PostgreSQL 13+）
jsonb_set_lax(target jsonb, path text[], new_value jsonb
[, create_if_missing boolean [, null_value_treatment text]]) → jsonb
-- 3. 插入：数组元素插在目标前/后；对象键仅在不存在时插入
jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean]) → jsonb
-- 4. 合并（操作符）
jsonb || jsonb → jsonb -- 数组拼接 / 对象键并集（右侧优先）；非递归，仅顶层
-- 5. 删除（操作符）
jsonb - text → jsonb -- 删对象键，或删数组中匹配的字符串元素
jsonb - text[] → jsonb -- 批量删（PostgreSQL 10+）
jsonb - integer → jsonb -- 按数组下标删（负数从尾部数）；非数组报错
jsonb #- text[] → jsonb -- 按路径删
-- 6. 清理与格式化
jsonb_strip_nulls(target jsonb [, strip_in_arrays boolean]) → jsonb -- 第二参为 PG18 新增
jsonb_pretty(target jsonb) → text最常用的是 jsonb_set 与 ||，前者改单点，后者做整体合并。下面这条是「用户配置覆盖默认配置」的标准写法：
-- 默认配置在后、用户配置在右，右侧同名键胜出
UPDATE user_profiles
SET settings = '{"theme": "light", "page_size": 20, "lang": "zh-CN"}'::jsonb || settings
WHERE id = 1;UPDATE 1详细说明
更新的本质：整值重写，没有原地修改
先在概念上把地基打牢，后面的性能与并发结论都是从这一点推出来的。
-- 观察「改一个键」到底重写了多少数据
SELECT
id,
pg_column_size(settings) AS size_before,
pg_column_size(jsonb_set(settings, '{theme}', '"dark"')) AS size_after
FROM user_profiles WHERE id = 1; id | size_before | size_after
----+-------------+------------
1 | 148 | 148
(1 row)体积没变，但整行都要重新写入：旧版本留在页里成为死元组，等 VACUUM 回收；如果 settings 超过 TOAST 阈值，行外的那一大块也要整体重写。
由此得到三条硬结论：
- 更新一个 1 KB 的文档和更新一个 1 MB 的文档，代价完全不同。前者可以每秒上万次，后者每秒可能只有几十次。
- 更新
jsonb列会持有整行排他锁，期间同一行的任何其他更新都要排队。 - 无法只更新半个文档。想减少代价，只能从「把大文档拆小」入手（见「最佳实践」）。
jsonb_set：按路径替换
jsonb_set 的路径是 text[]，元素可以是对象键或数组下标，写法与 #> 完全一致。
SELECT
jsonb_set('{"a": 1, "b": {"c": 2}}'::jsonb, '{a}', '10'::jsonb) AS set_top,
jsonb_set('{"a": 1, "b": {"c": 2}}'::jsonb, '{b,c}', '"x"'::jsonb) AS set_nested,
jsonb_set('{"a": 1, "b": {"c": 2}}'::jsonb, '{b,d}', 'true'::jsonb) AS set_new_key,
jsonb_set('{"a": 1, "b": {"c": 2}}'::jsonb, '{b,c}', '"x"'::jsonb, false) AS no_create,
jsonb_set('{"a": 1, "b": {"c": 2}}'::jsonb, '{z,y}', '"x"'::jsonb) AS missing_parent; set_top | set_nested | set_new_key | no_create | missing_parent
----------------+-------------------+---------------------------+-------------------+-------------------
{"a": 10, "b": {"c": 2}} | {"a": 1, "b": {"c": "x"}} | {"a": 1, "b": {"c": 2, "d": true}} | {"a": 1, "b": {"c": 2}} | {"a": 1, "b": {"c": 2}}
(1 row)把结果竖过来看更清楚：
| 列 | 路径 | 结果要点 |
|---|---|---|
set_top | {a} | 顶层键直接替换 |
set_nested | {b,c} | 「中间步骤必须已存在」——b 存在，所以能进到 c |
set_new_key | {b,d} | d 不存在，但 create_if_missing 默认为 true，于是新建 |
no_create | {b,c} | create_if_missing = false，路径存在则替换，不存在则原样返回 |
missing_parent | {z,y} | 中间步骤 z 不存在，整个语句静默返回原值，不报错 |
最后一行是最危险的：写错中间层键名，SQL 不报错、影响 0 行也不会提示，只有数据没变。
数组下标同样支持，且负数从尾部数：
SELECT
jsonb_set('[1, 2, 3]'::jsonb, '{0}', '"x"'::jsonb) AS idx_first,
jsonb_set('[1, 2, 3]'::jsonb, '{-1}', '"x"'::jsonb) AS idx_last,
jsonb_set('[1, 2, 3]'::jsonb, '{9}', '"x"'::jsonb) AS idx_oob_pos,
jsonb_set('[1, 2, 3]'::jsonb, '{-9}', '"x"'::jsonb) AS idx_oob_neg; idx_first | idx_last | idx_oob_pos | idx_oob_neg
-----------+----------+-------------+---------------
["x", 2, 3] | [1, 2, "x"] | [1, 2, 3, "x"] | ["x", 1, 2, 3]
(1 row)下标越界且 create_if_missing = true 时不会报错，而是追加：正数越界追加到尾部，负数越界追加到头部。
new_value 为 NULL 的陷阱与 jsonb_set_lax
这是 jsonb_set 最容易出事的地方，先看实测：
SELECT
jsonb_set('{"a": 1}'::jsonb, '{a}', NULL) AS set_sql_null,
jsonb_set('{"a": 1}'::jsonb, '{a}', 'null'::jsonb) AS set_json_null,
pg_typeof(jsonb_set('{"a": 1}'::jsonb, '{a}', NULL)) AS result_type; set_sql_null | set_json_null | result_type
--------------+---------------+-------------
| {"a": null} | jsonb
(1 row)区别只有一处：SQL NULL 与 JSON null 不是一回事。
- 第三参传 SQL
NULL（如payload ->> 'x'取不到值时），jsonb_set直接返回 SQL NULL——整列会被写成 NULL，若列上有NOT NULL约束则直接报错。 - 第三参传
'null'::jsonb，写入的才是 JSON null。
PostgreSQL 13 起提供了 jsonb_set_lax，把「传 NULL 怎么办」变成显式策略：
null_value_treatment | 行为 | 典型用途 |
|---|---|---|
'raise_exception' | 抛异常 | 严格校验，NULL 属于程序错误 |
'use_json_null'（默认） | 写成 JSON null | 与 jsonb_set(..., 'null'::jsonb) 等效 |
'delete_key' | 删除该路径的键 | 「传 null 表示取消设置」的 API 语义 |
'return_target' | 原样返回 target | 空值即忽略，保持数据不变 |
SELECT
jsonb_set_lax('{"a": 1, "b": 2}'::jsonb, '{a}', NULL) AS default_json_null,
jsonb_set_lax('{"a": 1, "b": 2}'::jsonb, '{a}', NULL, true, 'delete_key') AS delete_on_null,
jsonb_set_lax('{"a": 1, "b": 2}'::jsonb, '{a}', NULL, true, 'return_target') AS keep_on_null; default_json_null | delete_on_null | keep_on_null
-------------------------+---------------------+-------------------
{"a": null, "b": 2} | {"b": 2} | {"a": 1, "b": 2}
(1 row)第 2、3 个参数仍需照常给出路径与前三个位置参数，第 4 个是 create_if_missing，第 5 个才是策略。
'raise_exception' 的效果：
-- 错误！策略要求 NULL 必须报错
SELECT jsonb_set_lax('{"a": 1}'::jsonb, '{a}', NULL, true, 'raise_exception');ERROR: null_value_treatment must be use_json_null, delete_key, return_target, or raise_exceptionjsonb_insert：按路径插入
jsonb_insert 与 jsonb_set 的差别在于：它不覆盖，只在合适的位置插入。
SELECT
jsonb_insert('{"a": [0, 1, 2]}'::jsonb, '{a,1}', '"new"'::jsonb) AS before_target,
jsonb_insert('{"a": [0, 1, 2]}'::jsonb, '{a,1}', '"new"'::jsonb, true) AS after_target,
jsonb_insert('{"a": 1}'::jsonb, '{b}', '2'::jsonb) AS new_object_key,
jsonb_insert('{"a": 1}'::jsonb, '{a}', '2'::jsonb) AS existing_key; before_target | after_target | new_object_key | existing_key
----------------------+---------------------+----------------+----------------
{"a": [0, "new", 1, 2]} | {"a": [0, 1, "new", 2]} | {"a": 1, "b": 2} | {"a": 1}
(1 row)四条规则：
- 目标是数组元素时，插在该元素之前；
insert_after = true则插在之后。 - 目标是对象字段时，只有当对象里没有这个键才插入；键已存在则静默 no-op（见
existing_key列）。 - 所有前置步骤都必须存在，否则原样返回。这一点与
jsonb_set一致。 - 最后一步是越界数组下标时，正数追加到尾部，负数追加到头部。
第 3 条的实际后果值得单独看一眼：
-- 前置步骤 x 不存在，整条语句静默返回原值
SELECT jsonb_insert('{"a": {"b": 1}}'::jsonb, '{x,y}', '2'::jsonb) AS missing_parent; missing_parent
----------------
{"a": {"b": 1}}
(1 row)||：合并与它的「不递归」陷阱
|| 是最常用的合并手段，规则来自官方定义，逐条实测：
SELECT
'["a", "b"]'::jsonb || '["a", "d"]'::jsonb AS array_concat,
'{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb AS object_union,
'{"a": "b"}'::jsonb || '{"a": "z"}'::jsonb AS right_wins,
'[1, 2]'::jsonb || '3'::jsonb AS scalar_appended,
'{"a": "b"}'::jsonb || '42'::jsonb AS object_becomes_array; array_concat | object_union | right_wins | scalar_appended | object_becomes_array
------------------------+-------------------+--------------+-----------------+----------------------
["a", "b", "a", "d"] | {"a": "b", "c": "d"} | {"a": "z"} | [1, 2, 3] | [{"a": "b"}, 42]
(1 row)三个要点：
- 对象 + 对象 = 键并集，右侧优先（
right_wins里a变成了右侧的"z"）。 - 数组 + 标量 = 把标量当单元素数组追加。
- 对象 + 标量会退化成数组（
object_becomes_array）——这通常不是你想要的结果，混用类型时要格外小心。
最关键的规则是不递归：
SELECT
'{"a": {"x": 1, "y": 2}}'::jsonb || '{"a": {"x": 9}}'::jsonb AS shallow_merge,
jsonb_set('{"a": {"x": 1, "y": 2}}'::jsonb, '{a,x}', '9'::jsonb) AS deep_replace; shallow_merge | deep_replace
------------------------------+------------------------
{"a": {"x": 9}} | {"a": {"x": 9, "y": 2}}
(1 row)|| 只合并顶层：a 这个键在右侧存在，于是整个子对象被替换，y 消失了。要做「深合并」，必须用 jsonb_set 逐层指定路径。
「默认值 + 用户值」是 || 最经典的正确用法，因为它恰好只需要顶层合并：
-- 默认值在左、用户值在右：用户显式设置过的键胜出，没设置的保留默认
SELECT '{"theme": "light", "page_size": 20, "lang": "zh-CN"}'::jsonb || '{"theme": "dark"}'::jsonb AS merged; merged
---------------------------------------------------------------------
{"lang": "zh-CN", "theme": "dark", "page_size": 20}
(1 row)注意 jsonb 会按键重排（lang、theme、page_size 的内部字节序），输出的顺序与写入顺序无关，这是 jsonb 的固有特性。
要「把整个数组当成一个元素」追加，需要额外套一层数组：
SELECT
'[1, 2]'::jsonb || '[3, 4]'::jsonb AS flattened,
'[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb) AS nested; flattened | nested
--------------+----------------
[1, 2, 3, 4] | [1, 2, [3, 4]]
(1 row)- 与 #-：删除键、元素与路径
删除有三个操作符，覆盖三种粒度。
SELECT
'{"a": "b", "c": "d"}'::jsonb - 'a' AS del_key,
'["a", "b", "c", "b"]'::jsonb - 'b' AS del_array_value,
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] AS del_multi,
'["a", "b", "c"]'::jsonb - 1 AS del_index,
'["a", {"b": 1}]'::jsonb #- '{1,b}' AS del_by_path; del_key | del_array_value | del_multi | del_index | del_by_path
----------+-----------------+-----------+-----------+-------------
{"c": "d"} | ["a", "c"] | {} | ["a", "c"] | ["a", {}]
(1 row)逐个对照：
| 写法 | 语义 | 说明 |
|---|---|---|
- 'a' | 对象删键 / 数组删所有匹配元素 | 数组里删除的是「值等于该字符串」的元素 |
- '{a,c}'::text[] | 批量删 | PostgreSQL 10 起支持 |
- 1 | 删数组下标 1 的元素 | 负数从尾部数；非数组直接报错 |
#- '{1,b}' | 按路径删 | 路径中间步骤必须存在 |
- integer 的类型限制是报错而不是静默：
-- 错误！- integer 只能作用于数组
SELECT '{"a": 1}'::jsonb - 0;ERROR: cannot delete from object using integer index- 'a' 作用在数组上时，删除的是元素本身，不是下标：
SELECT
'["a", "b", "a"]'::jsonb - 'a' AS remove_all_string_a,
'["a", "b", "a"]'::jsonb - 0 AS remove_by_index; remove_all_string_a | remove_by_index
---------------------+-----------------
["b"] | ["b", "a"]
(1 row)第一列把两个 "a" 都删掉了，第二列只删了下标 0 那一个。两者语义完全不同，别写混。
还需要注意的是，- 'a' 只按顶层键名或数组字符串元素匹配，无法删除嵌套深处的键；删嵌套键要走 #- 路径：
SELECT
'{"user": {"name": "alice", "tmp": 1}}'::jsonb #- '{user,tmp}' AS del_nested,
'{"user": {"name": "alice", "tmp": 1}}'::jsonb - 'tmp' AS wrong_way; del_nested | wrong_way
----------------------------------+------------------------------
{"user": {"name": "alice"}} | {"user": {"name": "alice", "tmp": 1}}
(1 row)右侧看起来「应该删掉 tmp」，但它只匹配顶层键，所以什么都没删，也不报错。
数组的增删改：把三种操作对照起来
数组场景容易混淆，集中对照一次。假设初始值为 ["a", "c"]：
| 需求 | 写法 | 结果 |
|---|---|---|
| 尾部追加 | arr || '["d"]'::jsonb | ["a", "c", "d"] |
| 头部插入 | jsonb_insert(arr, '{0}', '"z"'::jsonb) | ["z", "a", "c"] |
| 指定位置插入 | jsonb_insert(arr, '{1}', '"b"'::jsonb) | ["a", "b", "c"] |
| 按下标替换 | jsonb_set(arr, '{1}', '"B"'::jsonb) | ["a", "B"] |
| 按下标删除 | arr - 0 | ["c"] |
| 按值删除 | arr - 'a' | ["c"] |
| 删除全部元素 | arr - '{a,c}'::text[] | [] |
-- 用一组并排表达式验证上表
SELECT
'["a", "c"]'::jsonb || '["d"]'::jsonb AS append_tail,
jsonb_insert('["a", "c"]'::jsonb, '{0}', '"z"'::jsonb) AS prepend,
jsonb_insert('["a", "c"]'::jsonb, '{1}', '"b"'::jsonb) AS insert_middle,
jsonb_set('["a", "c"]'::jsonb, '{1}', '"B"'::jsonb) AS replace_index,
'["a", "c"]'::jsonb - 0 AS del_index,
'["a", "c"]'::jsonb - 'a' AS del_value; append_tail | prepend | insert_middle | replace_index | del_index | del_value
-------------+------------+---------------+---------------+-----------+-----------
["a", "c", "d"] | ["z", "a", "c"] | ["a", "b", "c"] | ["a", "B"] | ["c"] | ["c"]
(1 row)jsonb_strip_nulls：清理空字段
更新 JSON 时，最常见的「脏数据」是值为 null 的字段。jsonb_strip_nulls 递归删除对象中值为 null 的字段：
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}, "f": [1, null, 3]}'::jsonb) AS stripped; stripped
------------------------------------------------------
{"a": 1, "c": {"e": 2}, "f": [1, null, 3]}
(1 row)b 与嵌套的 c.d 都被删掉了，但数组里的 null 保留。这是 PostgreSQL 17 及之前的行为。
PostgreSQL 18 给这两个函数加了第二个布尔参数 strip_in_arrays（默认 false，保持向后兼容）：
-- PostgreSQL 18+
SELECT
jsonb_strip_nulls('[5, 6, null, null, 9]'::jsonb) AS keep_array_nulls,
jsonb_strip_nulls('[5, 6, null, null, 9]'::jsonb, true) AS strip_array_nulls,
jsonb_strip_nulls('[null]'::jsonb, true) AS single_null_array; keep_array_nulls | strip_array_nulls | single_null_array
----------------------+-------------------+-------------------
[5, 6, null, null, 9] | [5, 6, 9] | []
(1 row)在 PostgreSQL 18 之前，要删除数组内的 null，只能借助 jsonpath 或「展开再聚合」：
-- PostgreSQL 18 之前的替代方案一：jsonpath（一步到位）
SELECT jsonb_path_query_array('[5, 6, null, null, 9]'::jsonb, '$[*] ? (@ != null)') AS via_jsonpath;
-- 替代方案二：展开再聚合（需要两次转换，代价更高）
SELECT jsonb_agg(e) AS via_agg
FROM jsonb_array_elements('[5, 6, null, null, 9]'::jsonb) AS e
WHERE e IS NOT NULL; via_jsonpath | via_agg
--------------+------------
[5, 6, 9] | [5, 6, 9]
(1 row)还可以用「对象中剔除 null 值」的思路做体量优化：
SELECT
jsonb_strip_nulls('{"referer": null, "url": "/index", "duration": 320}'::jsonb) AS slimmed; slimmed
-----------------------------------------------------------
{"url": "/index", "duration": 320}
(1 row)需要注意：整个文档就是一个裸 null 时不受影响——jsonb_strip_nulls('null'::jsonb, true) 仍然返回 null。
jsonb_pretty：输出格式化
jsonb_pretty 只做展示，返回 text 而不是 jsonb：
SELECT jsonb_pretty('{"a": 1, "b": {"c": [1, 2]}}'::jsonb) AS pretty; pretty
-------------------------
{ +
"a": 1, +
"b": { +
"c": [ +
1, +
2 +
] +
} +
}
(1 row)（上面 + 号是 psql 的续行标记，实际是一段带缩进的多行文本。）
它可以用在日志、错误消息、psql 手工排查里，但不要用在应用接口的返回路径上：因为返回类型是 text，还要再多一次解析，而且缩进会显著放大体积。
更新代价、行锁与并发丢更新
前面提到「整值重写」，具体到并发场景，最典型的坑是读改写丢更新：
-- 危险的两步写法（伪代码）
-- 1) 应用层读出: SELECT settings FROM user_profiles WHERE id = 1;
-- 2) 应用层改完写回: UPDATE user_profiles SET settings = '<改后的完整 JSON>' WHERE id = 1;两个并发请求都读到同一份旧值，各自改一个键后写回，后写的一方会覆盖前一方，前一个键的修改凭空消失。正确做法是把「修改」放进一条 SQL，让数据库在一次操作里完成：
-- 推荐：读改写在同一条语句内完成，天然原子
UPDATE user_profiles
SET settings = jsonb_set(settings, '{theme}', '"dark"'::jsonb),
updated_at = now()
WHERE id = 1;UPDATE 1这条语句的执行过程是：取行 → 加行锁 → 用当前值计算新值 → 写回。若同一行有并发更新，后到的事务会在锁上等待，等拿到最新版本再计算，因此不会丢更新。
需要分多步处理时，用显式行锁：
BEGIN;
-- 加行锁，阻止其他事务修改这一行
SELECT settings FROM user_profiles WHERE id = 1 FOR UPDATE;
-- ... 应用层计算新值 ...
UPDATE user_profiles SET settings = $1 WHERE id = 1;
COMMIT;代价方面还有两点值得注意：
| 现象 | 原因 | 缓解方式 |
|---|---|---|
| 更新变慢、表持续膨胀 | 每次更新产生整行新版本，旧版本等 VACUUM | 控制更新频率，及时 VACUUM / 调 autovacuum |
| 写入延迟抖动 | 超过 TOAST 阈值后行外数据整体重写 | 把大文档拆成多行/多表 |
| GIN 索引写放大 | 每个索引项都要增删 | 只给真正用于筛选的 jsonb 列建 GIN 索引 |
实战示例
下面用 user_profiles 表完整走一遍「用户配置」的更新流程：建表、改单键、多层改、合并默认值、数组增删、按值删除、清理空值。
第一步：建表
DROP TABLE IF EXISTS user_profiles;
CREATE TABLE user_profiles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
username varchar(64) NOT NULL UNIQUE,
settings jsonb NOT NULL DEFAULT '{}'::jsonb,
updated_at timestamptz NOT NULL DEFAULT now(),
-- 守护结构：settings 必须是对象，禁止写入数组或标量
CONSTRAINT ck_settings_is_object CHECK (jsonb_typeof(settings) = 'object'),
-- 守护体积：单个配置文档不超过 8 KB，防止无限制膨胀
CONSTRAINT ck_settings_size CHECK (pg_column_size(settings) < 8192)
);DROP TABLE
CREATE TABLE第二步：写入初始数据
INSERT INTO user_profiles (username, settings) VALUES
('alice', '{"theme": "light", "page_size": 20, "tags": ["vip", "beta"]}'),
('bob', '{"theme": "dark", "lang": "en", "notify": {"email": null, "sms": true}}');INSERT 0 2第三步：改单个键
-- 把 alice 的主题改成 dark
UPDATE user_profiles
SET settings = jsonb_set(settings, '{theme}', '"dark"'::jsonb),
updated_at = now()
WHERE username = 'alice'
RETURNING username, settings; username | settings
----------+------------------------------------------------------------
alice | {"tags": ["vip", "beta"], "theme": "dark", "page_size": 20}
(1 row)第四步：改嵌套键，并新建一个缺失的键
UPDATE user_profiles
SET settings = jsonb_set(
jsonb_set(settings, '{notify,sms}', 'false'::jsonb), -- 已存在，直接替换
'{notify,push}', 'true'::jsonb -- 不存在，缺失即创建
),
updated_at = now()
WHERE username = 'bob'
RETURNING username, settings; username | settings
----------+-----------------------------------------------------------------------------------------
bob | {"lang": "en", "theme": "dark", "notify": {"sms": false, "push": true, "email": null}}
(1 row)两个 jsonb_set 嵌套，是因为一次只能改一个路径。想少写一层嵌套，可以用 || 做辅助（仅当需要合并的层级是顶层时）。
第五步：用 || 合并默认配置
新用户注册时，把系统默认配置与用户提交的配置合并：
UPDATE user_profiles
SET settings = '{"theme": "light", "page_size": 20, "lang": "zh-CN", "notify": {"email": true}}'::jsonb
|| settings, -- 左侧默认值、右侧用户值，用户值优先
updated_at = now()
WHERE username = 'bob'
RETURNING username, settings; username | settings
----------+-----------------------------------------------------------------------------------------
bob | {"lang": "en", "theme": "dark", "page_size": 20, "notify": {"sms": false, "push": true, "email": null}}
(1 row)lang、theme 保留了用户值，缺失的 page_size 从默认值补上。但注意 notify 是整体替换的——默认值里的 {"email": true} 没有生效，因为 || 不递归，右侧的 notify 整个覆盖了左侧。这正是前面强调的「不递归」陷阱在真实场景里的样子。
第六步：数组操作
UPDATE user_profiles
SET settings = settings || '["promo"]'::jsonb, -- 尾部追加一个标签
settings = jsonb_set(settings || '["promo"]'::jsonb, '{tags,0}', '"svip"'::jsonb),
updated_at = now()
WHERE username = 'alice'
RETURNING username, settings -> 'tags' AS tags; username | tags
----------+---------------------
alice | ["svip", "beta"]
(1 row)⚠️ 上面这条 SQL 里 settings 被赋了两次值，在 PostgreSQL 中是未定义行为，不要这样写。正确写法是把表达式的嵌套理清，一条语句只赋一次：
-- 正确：先在下标 0 处替换，再追加一个新元素
UPDATE user_profiles
SET settings = jsonb_set(settings, '{tags,0}', '"svip"'::jsonb) || '["promo"]'::jsonb,
updated_at = now()
WHERE username = 'alice'
RETURNING username, settings -> 'tags' AS tags; username | tags
----------+-----------------------
alice | ["svip", "beta", "promo"]
(1 row)第七步：删除键、按值删除数组元素、按路径删除
SELECT
jsonb_set('{}'::jsonb, '{x}', '1'::jsonb) AS keep,
'{"a": 1, "b": 2}'::jsonb - 'a' AS drop_key,
'{"tags": ["vip", "beta", "vip"]}'::jsonb #- '{tags,1}' AS drop_index_path,
'["vip", "beta", "vip"]'::jsonb - '{vip}'::text[] AS drop_all_vip; keep | drop_key | drop_index_path | drop_all_vip
---------+-----------+----------------------------+--------------
{"x": 1} | {"b": 2} | {"tags": ["vip", "vip"]} | ["beta"]
(1 row)组合到 UPDATE 里，实现「用户取消了某个标签」：
UPDATE user_profiles
SET settings = jsonb_set(settings, '{tags}', (settings -> 'tags') - 'beta'),
updated_at = now()
WHERE username = 'alice'
RETURNING username, settings -> 'tags' AS tags; username | tags
----------+---------------
alice | ["svip", "promo"]
(1 row)第八步：清理空值
UPDATE user_profiles
SET settings = jsonb_strip_nulls(settings),
updated_at = now()
WHERE username = 'bob'
RETURNING username, settings; username | settings
----------+-----------------------------------------------------------------------------
bob | {"lang": "en", "theme": "dark", "page_size": 20, "notify": {"sms": false, "push": true}}
(1 row)notify.email 这个 null 字段被删掉了。
第九步：在一条语句里完成多处修改（推荐形态）
实际项目里更常见的是「一次请求改多个字段」，把所有修改串成一个表达式即可：
UPDATE user_profiles
SET settings = jsonb_set(
jsonb_set(
jsonb_set(settings, '{theme}', '"dark"'::jsonb),
'{lang}', '"zh-CN"'::jsonb
),
'{notify,email}', 'true'::jsonb
),
updated_at = now()
WHERE id = 1
RETURNING id, settings; id | settings
----+----------------------------------------------------------------------------------------
1 | {"lang": "zh-CN", "tags": ["svip"], "theme": "dark", "notify": {"email": true}, "page_size": 20}
(1 row)第十步：清理
DROP TABLE user_profiles;DROP TABLE注意事项
jsonb没有字段级更新。任何一次修改都是整值重写、整行写入。文档越大，更新越慢；「只要改一个键」不会让它变快。jsonb_set的第三参传 SQL NULL 会让整列变成 NULL，而不是写入 JSON null。要用 JSON null 就显式写'null'::jsonb，或者改用jsonb_set_lax指定策略：sql-- 来自 JSON 外部的值可能是 SQL NULL UPDATE t SET doc = jsonb_set(doc, '{nick}', to_jsonb(v_nick)) WHERE id = 1; -- 若 v_nick 为 NULL，to_jsonb(NULL) 得到的是 SQL NULL SELECT to_jsonb(NULL::text) IS NULL AS is_sql_null;textis_sql_null ------------- t (1 row)所以「把变量值写进 JSON」时，要么用
to_jsonb(COALESCE(v, ''))兜底，要么用jsonb_set_lax(..., 'use_json_null')。中间路径不存在时
jsonb_set/jsonb_insert静默返回原值。不报错、不提示、不影响任何行数统计。改动后务必用RETURNING或复查一次。jsonb_insert对已存在的对象键是 no-op。它不做覆盖；要覆盖得用jsonb_set。||不递归。嵌套对象是整体替换，不是深合并。想深合并只能逐层jsonb_set，或自己写递归函数。||遇到「对象 + 标量」会退化成数组：sqlSELECT '{"a": "b"}'::jsonb || '42'::jsonb AS weird;textweird ---------------- [{"a": "b"}, 42] (1 row)结果类型从对象变成数组，若列上有机型约束会立刻报错，没有约束则可能污染下游。
- integer只能作用于数组，且会报错：sql-- 错误！对象不能按整数下标删除 SELECT '{"a": 1}'::jsonb - 0;textERROR: cannot delete from object using integer index- 'x'只匹配顶层。删嵌套键要用#-路径；写在数组上是「按值删除所有匹配元素」，不是按下标。不要在同一条
UPDATE的SET列表里对同一列赋值两次（如SET settings = ..., settings = ...）。PostgreSQL 不保证取哪个表达式的结果，属于未定义行为。要把多个修改串成一个嵌套表达式。jsonb_strip_nulls不影响裸 null。整个文档是null时原样返回；strip_in_arrays参数（PostgreSQL 18+）也只管数组元素，不管「文档本身是 null」。jsonb_pretty返回text。它不能参与jsonb运算，写进jsonb列需要再::jsonb转一次，且缩进会放大体积，不适合做持久化。读改写会丢更新。跨语句的「读出来改完写回」在并发下一定出问题，必须把修改压进一条 SQL，或用
SELECT ... FOR UPDATE加行锁。更新
jsonb列会同步维护它上面的 GIN 索引，写入代价随索引数量放大。索引不是免费的，只为真实筛选条件建。
最佳实践
把修改压成一条
UPDATE表达式，永远不要在应用层「读—改—写」：sqlUPDATE user_profiles SET settings = jsonb_set(settings, '{theme}', '"dark"'::jsonb), updated_at = now() WHERE id = 1;「默认值 + 用户值」用
||做顶层合并，顺序固定为「默认在左、用户在右」：sqlSET settings = '{"theme": "light", "page_size": 20}'::jsonb || $1::jsonb但要清楚它只合并顶层；嵌套结构的默认值需要单独用
jsonb_set补齐。需要「传 NULL 表示取消设置」的 API 语义时用
jsonb_set_lax(..., 'delete_key')，让数据库承担这个约定，避免在应用层写if/else分支。多字段修改用嵌套
jsonb_set或jsonb_build_object构造整体新值。前者适合「保留原有其他键」，后者适合「结构由服务端完整决定」。更新后立刻在
WHERE中确认命中行数，必要时用RETURNING回读校验：sqlUPDATE t SET doc = jsonb_set(doc, '{a}', '1'::jsonb) WHERE id = 1 RETURNING doc;高频更新的字段抽成普通列。计数器、状态机、
updated_at这类字段放在jsonb里会引发整值重写；把它们提升为独立列，jsonb只放「整体读取、整份替换」的数据。超大文档拆行/拆表。与其维护一个 500 KB 的
settings，不如按业务域拆成多行（一行一个配置域），每次只更新对应行。这直接把整值重写的影响面缩小了一个数量级。给
jsonb列加jsonb_typeof检查约束，避免||的「对象变数组」之类意外把数据结构写坏：sqlCONSTRAINT ck_settings_is_object CHECK (jsonb_typeof(settings) = 'object')用
pg_column_size监控文档体积，在膨胀到 TOAST 之前就做拆分决策：sqlSELECT id, pg_column_size(settings) AS bytes FROM user_profiles ORDER BY bytes DESC LIMIT 10;并发敏感场景加乐观锁。用
updated_at或独立版本列做条件更新，冲突时重试：sqlUPDATE user_profiles SET settings = jsonb_set(settings, '{theme}', '"dark"'::jsonb), updated_at = now() WHERE id = 1 AND updated_at = $expected_ts; -- 影响 0 行表示版本已变，交由应用层重试更新语句先在只读副本或测试库跑一次
EXPLAIN (ANALYZE, BUFFERS)，确认没有意外的顺序扫描或索引维护开销。
下一节
继续学习：JSONB 的索引优化