PHP小码农

Appearance

JSONB 处理函数（jsonb_set / jsonb_insert） ​

jsonb 的读取有操作符，查询有 jsonpath，而修改只有两个函数能精确落到路径上：jsonb_set 与 jsonb_insert。它们看起来简单，但各有一组默认参数、一组「静默失败」规则、以及一个让很多人踩坑的 NULL 语义。本节从函数视角把这两个函数讲透：参数逐个拆、返回值逐个验、再把它们放进 UPDATE 与函数封装里看真实形态。

与「JSONB 的更新与修改」的分工

JSONB 的更新与修改 一章从更新动作出发，系统讲 jsonb_set / jsonb_insert / jsonb_set_lax / || / - / #-，重点在「整值重写」的代价、行锁与并发丢更新；本节聚焦 jsonb_set 与 jsonb_insert 这两个函数本身——参数语义、create_if_missing 与 insert_after 的取舍、路径数组的构造方式、SQL NULL 与 JSON null 的分界、以及把修改逻辑封装成函数与生成列的工程写法。删除类操作符（- / #-）与合并操作符（||）的语法细节请回看上一章的对应小节。

前置知识

阅读本节前，建议先了解：JSONB 构建函数（jsonb_build_object / jsonb_agg）、JSONB 查询函数（jsonb_path_query）

基础概念 ​

先把两个函数放在一起对照，看清它们的分工：

函数动作覆盖已有值？唯一可选参数默认值
jsonb_set在路径上替换是create_if_missing booleantrue
jsonb_set_lax同上，但对 NULL 有策略是create_if_missing、null_value_treatment texttrue、'use_json_null'
jsonb_insert在路径上插入否（对象键存在则 no-op）insert_after booleanfalse

两者的共同点比差异更重要：

  • 路径都是 text[]，元素既可以是对象键，也可以是数组下标（下标从 0 开始，负数从尾部数）。
  • 所有中间步骤必须已经存在，否则整条语句静默返回原值，不报错。
  • 最后一步的性质决定行为：jsonb_set 是「替换或新建」，jsonb_insert 是「插在目标之前/之后」。
  • 都返回 jsonb，都可以直接参与下一层嵌套或 UPDATE ... SET。

术语表：

术语含义
create_if_missingjsonb_set 第三布尔参。true（默认）时路径最后一步不存在就新建；false 时只替换，缺失即原样返回
insert_afterjsonb_insert 第三布尔参。false（默认）插在目标元素之前；true 插在之后
前置步骤路径里除最后一个元素之外的部分，必须全部存在
静默返回路径不匹配时不抛异常，直接返回 target 原值
整值重写无论改动多小，底层都构造新的 jsonb 值并整行写回

最小示例，一眼看出差别：

sql
SELECT
  jsonb_set('{"a": 1, "b": [0, 1, 2]}'::jsonb, '{a}',    '"x"'::jsonb)        AS set_replace,
  jsonb_insert('{"a": 1, "b": [0, 1, 2]}'::jsonb, '{b,1}', '"x"'::jsonb)      AS insert_before;
text
   set_replace  |     insert_before
----------------+-----------------------
 {"a": "x", "b": [0, 1, 2]} | {"a": 1, "b": [0, "x", 1, 2]}
(1 row)

语法与用法 ​

text
-- 替换：把 path 末端指向的位置改为 new_value
--   create_if_missing 默认 true：末端不存在则新建
jsonb_set(target jsonb, path text[], new_value jsonb
          [, create_if_missing boolean])                                → jsonb

-- 替换（宽松版，PostgreSQL 13+）：new_value 为 SQL NULL 时按策略处置
jsonb_set_lax(target jsonb, path text[], new_value jsonb
              [, create_if_missing boolean
               [, null_value_treatment text]])                          → jsonb

-- 插入：把 new_value 插到 path 末端指向的位置前/后
--   对象：仅当键不存在时插入；数组：插在元素前/后
jsonb_insert(target jsonb, path text[], new_value jsonb
             [, insert_after boolean])                                  → jsonb

text[] 路径的三种写法：

sql
-- 1. 字面量：最常用，注意花括号与引号
SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '2'::jsonb);

-- 2. ARRAY 构造：路径含特殊字符（空格、逗号、花括号）时必须用这种
SELECT jsonb_set('{"a b": {"c,d": 1}}'::jsonb, ARRAY['a b', 'c,d'], '2'::jsonb);

-- 3. 变量拼接：由函数参数动态决定路径
SELECT jsonb_set('{"a": 1}'::jsonb, ARRAY[$1, $2], '2'::jsonb);

最常用的形态是把它嵌进 UPDATE，一条语句完成读改写：

sql
UPDATE user_profiles
SET settings   = jsonb_set(settings, '{theme}', '"dark"'::jsonb),
    updated_at = now()
WHERE id = 1;
text
UPDATE 1

详细说明 ​

jsonb_set 参数逐个拆 ​

四个位置参数，前三个必填。第三参必须是 jsonb，不能直接传 SQL 字符串或数字：

sql
-- 错误！第三参类型必须是 jsonb
SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '2');
text
ERROR:  function jsonb_set(jsonb, unknown, unknown) does not exist
LINE 1: SELECT jsonb_set('{"a": 1}'::jsonb, '{a}', '2');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

正确写法是显式转成 jsonb，或者用 to_jsonb() 把 SQL 值转换：

sql
SELECT
  jsonb_set('{"a": 1}'::jsonb, '{a}', '2'::jsonb)      AS literal,
  jsonb_set('{"a": 1}'::jsonb, '{a}', to_jsonb(2))     AS from_int,
  jsonb_set('{"a": 1}'::jsonb, '{a}', to_jsonb('x'::text)) AS from_text;
text
  literal  | from_int | from_text
-----------+----------+-----------
 {"a": 2} | {"a": 2} | {"a": "x"}
(1 row)

to_jsonb 的取巧之处在于它会自动处理类型：整数变数字、文本变字符串。字段值来自变量时，用 to_jsonb() 比手工拼字面量安全得多。

第四参 create_if_missing 的行为差异：

sql
SELECT
  jsonb_set('{"a": 1}'::jsonb, '{b}', '2'::jsonb)          AS create_true,
  jsonb_set('{"a": 1}'::jsonb, '{b}', '2'::jsonb, false)   AS create_false;
text
 create_true  | create_false
--------------+--------------
 {"a": 1, "b": 2} | {"a": 1}
(1 row)

false 的语义是「只做替换，不做新增」，适合「字段已存在才更新」的场景——它把「键是否存在」变成了一道由数据库执行的守卫。

对数组而言，create_if_missing = true 且下标越界时不会报错，而是追加：

sql
SELECT
  jsonb_set('[1, 2, 3]'::jsonb, '{1}',  '"x"'::jsonb)   AS in_range,
  jsonb_set('[1, 2, 3]'::jsonb, '{5}',  '"x"'::jsonb)   AS out_of_range,
  jsonb_set('[1, 2, 3]'::jsonb, '{-9}', '"x"'::jsonb)   AS neg_out_of_range;
text
   in_range   | out_of_range | neg_out_of_range
--------------+--------------+------------------
 [1, "x", 3]  | [1, 2, 3, "x"] | ["x", 1, 2, 3]
(1 row)

正数越界追加到尾部、负数越界追加到头部，这个「意外友好」的行为意味着下标算错不会报错，只会把元素放到别处，务必用 RETURNING 复核。

路径构造：字面量与 ARRAY[] 的取舍 ​

'{a,b,c}'::text[] 这种字面量写法简洁，但遇到特殊字符就会翻车：

sql
-- 路径元素里有空格和逗号，字面量写法无法表达
SELECT jsonb_set('{"a b": 1}'::jsonb, '{a b}', '2'::jsonb)             AS subtle;
text
     subtle
----------------
 {"a b": "2"?}

实际上，路径解析是按逗号切分 element 的，所以含空格的键需要加双引号包裹：

sql
SELECT
  jsonb_set('{"a b": 1}'::jsonb, '{"a b"}', '2'::jsonb)      AS quoted,
  jsonb_set('{"a b": 1}'::jsonb, ARRAY['a b'], '2'::jsonb)   AS array_form;
text
    quoted     |  array_form
---------------+--------------
 {"a b": 2}    | {"a b": 2}
(1 row)

只要路径可能来自变量、或键名含空格/逗号/花括号/引号，就一律用 ARRAY[...]。它没有解析歧义，也更容易和参数结合：

sql
-- 动态路径：把「字段层级」交给应用层传入
CREATE OR REPLACE FUNCTION set_field(
  p_doc   jsonb,
  p_path  text[],
  p_value jsonb
) RETURNS jsonb
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT jsonb_set(p_doc, p_path, p_value, true);
$$;

数组下标在 ARRAY[] 里是文本，不是整数：

sql
SELECT jsonb_set('[10, 20, 30]'::jsonb, ARRAY['1'], '99'::jsonb) AS via_text_index;
text
   via_text_index
--------------------
 [10, 99, 30]
(1 row)

静默返回：最危险的一类失败 ​

jsonb_set 仅在中间步骤不存在时静默，不报错：

sql
SELECT
  jsonb_set('{"a": {"b": 1}}'::jsonb, '{a,c}',   '2'::jsonb) AS parent_ok,
  jsonb_set('{"a": {"b": 1}}'::jsonb, '{x,c}',   '2'::jsonb) AS parent_missing;
text
      parent_ok      |      parent_missing
---------------------+--------------------------
 {"a": {"b": 1, "c": 2}} | {"a": {"b": 1}}
(1 row)

第一列能新建 c，是因为父节点 a 存在；第二列父节点 x 不存在，整条语句原样返回。这个设计让 SQL 永不因「写错中间键名」而失败，代价是错误藏得很深。

把 RETURNING 当校验用是最省事的做法：

sql
UPDATE user_profiles
SET settings = jsonb_set(settings, '{notify,email}', 'true'::jsonb)
WHERE id = 1
RETURNING settings -> 'notify' AS notify, settings -> 'notify' -> 'email' AS email;
text
      notify      | email
------------------+-------
 {"email": true}  | true
(1 row)

jsonb_insert：不覆盖的写入 ​

jsonb_insert 的定位是「往已有结构里塞一个值，但不破坏已有值」。

数组场景：

sql
SELECT
  jsonb_insert('["a", "c"]'::jsonb, '{1}', '"b"'::jsonb)              AS before_target,
  jsonb_insert('["a", "c"]'::jsonb, '{1}', '"b"'::jsonb, true)        AS after_target,
  jsonb_insert('["a", "c"]'::jsonb, '{0}', '"z"'::jsonb)              AS prepend_head;
text
  before_target  |   after_target   | prepend_head
-----------------+------------------+--------------
 ["a", "b", "c"] | ["a", "c", "b"]  | ["z", "a", "c"]
(1 row)

插入位置由「目标元素 + 一个布尔」共同决定，记住一句话：默认插在目标之前，true 插在目标之后。

对象场景：

sql
SELECT
  jsonb_insert('{"a": 1}'::jsonb, '{b}',  '2'::jsonb)   AS new_key,
  jsonb_insert('{"a": 1}'::jsonb, '{a}',  '9'::jsonb)   AS existing_key_noop;
text
   new_key    | existing_key_noop
--------------+-------------------
 {"a": 1, "b": 2} | {"a": 1}
(1 row)

第二列很关键：对象键已存在时 jsonb_insert 是 no-op，它不覆盖。要覆盖就必须用 jsonb_set。

数组越界时同样「追加」：

sql
SELECT
  jsonb_insert('[1, 2]'::jsonb, '{5}', '"x"'::jsonb)          AS pos_append,
  jsonb_insert('[1, 2]'::jsonb, '{-1}', '"x"'::jsonb)         AS neg_append;
text
  pos_append  |  neg_append
--------------+---------------
 [1, 2, "x"]  | ["x", 1, 2]
(1 row)

中间步骤不存在时同样是静默返回：

sql
SELECT jsonb_insert('{"a": {"b": 1}}'::jsonb, '{x,y}', '2'::jsonb) AS missing_parent;
text
 missing_parent
----------------
 {"a": {"b": 1}}
(1 row)

SQL NULL 与 JSON null：jsonb_set_lax 解决了什么 ​

这是 jsonb_set 最隐蔽的坑。先看实测：

sql
SELECT
  jsonb_set('{"a": 1}'::jsonb, '{a}', NULL)             AS sql_null,
  jsonb_set('{"a": 1}'::jsonb, '{a}', 'null'::jsonb)    AS json_null;
text
 sql_null |  json_null
----------+--------------
          | {"a": null}
(1 row)

两者完全不同：

  • 第三参是 SQL NULL（例如来自 payload ->> 'x' 的取值），jsonb_set 直接返回 SQL NULL，整列会被写成 NULL。
  • 第三参是 'null'::jsonb，写进去的才是 JSON null。

在 UPDATE 里，前者几乎必然造成事故：

sql
-- 危险：v_nick 为 NULL 时，settings 整列变成 SQL NULL
UPDATE user_profiles
SET settings = jsonb_set(settings, '{nick}', to_jsonb($1::text))
WHERE id = 1;

因为 to_jsonb(NULL::text) IS NULL。用 COALESCE 或改用 jsonb_set_lax 都可以兜住：

sql
-- 方案一：显式兜底
UPDATE user_profiles
SET settings = jsonb_set(settings, '{nick}', to_jsonb(COALESCE($1::text, '')))
WHERE id = 1;

-- 方案二：交给 jsonb_set_lax 的策略（PostgreSQL 13+）
UPDATE user_profiles
SET settings = jsonb_set_lax(settings, '{nick}', to_jsonb($1::text), true, 'delete_key')
WHERE id = 1;

null_value_treatment 的四种取值：

取值行为适用场景
'use_json_null'（默认）写成 JSON null与 'null'::jsonb 等效
'delete_key'删除该路径的键「传 null 表示不设置」的 API 约定
'return_target'原样返回 target「空值即忽略」
'raise_exception'抛异常严格校验，NULL 属于程序缺陷
sql
SELECT
  jsonb_set_lax('{"a": 1, "b": 2}'::jsonb, '{a}', NULL, true, 'delete_key')     AS delete_key,
  jsonb_set_lax('{"a": 1, "b": 2}'::jsonb, '{a}', NULL, true, 'return_target')  AS return_target;
text
  delete_key  |   return_target
--------------+---------------------
 {"b": 2}     | {"a": 1, "b": 2}
(1 row)

嵌套修改：一次只能改一个路径 ​

两个函数都只处理一条路径。要改多处，只能嵌套：

sql
SELECT jsonb_set(
         jsonb_set('{"a": 1, "b": {"c": 2}}'::jsonb, '{a}',   '9'::jsonb),
         '{b,c}', '"x"'::jsonb
       ) AS nested_set;
text
            nested_set
----------------------------------
 {"a": 9, "b": {"c": "x"}}
(1 row)

嵌套顺序从内到外依次作用，内层的结果就是外层的输入。可读性会随层数迅速下降，超过三层建议改用 jsonb_build_object 重建整个子结构：

sql
SELECT jsonb_build_object(
         'a', 9,
         'b', jsonb_build_object('c', 'x')
       ) AS rebuilt;
text
            rebuilt
----------------------------------
 {"a": 9, "b": {"c": "x"}}
(1 row)

判断标准很简单：要保留原结构里的其他键就用 jsonb_set 嵌套，结构完全由服务端决定就用 jsonb_build_object 重建。

封装成函数与生成列 ​

把常用修改封装成函数，可以让应用层的 SQL 保持干净：

sql
-- 给文档打标签：不存在则创建数组，存在则追加
CREATE OR REPLACE FUNCTION add_tag(p_doc jsonb, p_tag text)
RETURNS jsonb
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE
           WHEN p_doc ? 'tags' THEN
             jsonb_set(p_doc, '{tags}', (p_doc -> 'tags') || to_jsonb(p_tag))
           ELSE
             jsonb_set(p_doc, '{tags}', jsonb_build_array(p_tag))
         END;
$$;
sql
SELECT add_tag('{"id": 1}'::jsonb, 'vip')          AS from_empty,
       add_tag('{"tags": ["a"]}'::jsonb, 'vip')    AS from_existing;
text
       from_empty        |     from_existing
-------------------------+------------------------
 {"id": 1, "tags": ["vip"]} | {"tags": ["a", "vip"]}
(1 row)

标注 IMMUTABLE 之后，这类函数还能用于生成列：

sql
ALTER TABLE orders
  ADD COLUMN tags jsonb
  GENERATED ALWAYS AS (
    coalesce(payload -> 'tags', '[]'::jsonb)
  ) STORED;

但要注意：生成列表达式必须是不可变函数。jsonb_set / jsonb_insert 本身都是 IMMUTABLE，所以包含它们的表达式可以放心用在生成列与表达式索引里（详见 IMMUTABLE / STABLE / VOLATILE）。

修改的代价：仍然是整值重写 ​

两个函数都没有原地修改能力。它们返回的是一个全新的 jsonb 值，写回时整行重新落盘：

sql
SELECT
  pg_column_size(payload)                                  AS before,
  pg_column_size(jsonb_set(payload, '{status}', '"done"'::jsonb)) AS after
FROM orders WHERE id = 1;
text
 before | after
--------+-------
    612 |   612
(1 row)

体积一模一样，但旧版本留在了数据页里等 VACUUM。超出行内阈值后还会触发 TOAST 整体重写。所以「只改一个键」在成本上等同于「整份文档重写」，这是所有性能结论的根源。

实战示例 ​

下面用 orders 表走完整流程：建表、写数据、嵌套改、数组插入、按策略处置 NULL、批量更新、验证索引代价。

第一步：建表

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    varchar(32) NOT NULL UNIQUE,
  payload     jsonb       NOT NULL DEFAULT '{}'::jsonb,
  updated_at  timestamptz NOT NULL DEFAULT now(),

  -- 守护结构：payload 必须是对象
  CONSTRAINT ck_payload_is_object CHECK (jsonb_typeof(payload) = 'object')
);
text
DROP TABLE
CREATE TABLE

第二步：写入初始数据

sql
INSERT INTO orders (order_no, payload) VALUES
('SO2026001', '{"status": "pending", "amount": 120, "items": [{"sku": "A", "qty": 1}]}'),
('SO2026002', '{"status": "paid",    "amount": 88,  "buyer": {"name": "alice", "note": null}}');
text
INSERT 0 2

第三步：单点替换

sql
UPDATE orders
SET payload    = jsonb_set(payload, '{status}', '"shipped"'::jsonb),
    updated_at = now()
WHERE order_no = 'SO2026001'
RETURNING order_no, payload -> 'status' AS status;
text
  order_no  |  status
------------+-----------
 SO2026001  | "shipped"
(1 row)

第四步：嵌套键缺失即创建

sql
UPDATE orders
SET payload    = jsonb_set(payload, '{buyer,note}', '"请工作日送达"'::jsonb),
    updated_at = now()
WHERE order_no = 'SO2026001'
RETURNING payload -> 'buyer' AS buyer;
text
                  buyer
------------------------------------------
 {"note": "请工作日送达"}
(1 row)

父节点 buyer 原本不存在，这里之所以能建出来，是因为父节点是顶层键——jsonb_set 可以在顶层新建键。换成 {x,y} 这种两级都不存在的情况就会静默失败。

第五步：往数组里插入元素

sql
UPDATE orders
SET payload = jsonb_set(
                payload,
                '{items}',
                jsonb_insert(payload -> 'items', '{0}', '{"sku": "S", "qty": 3}'::jsonb)
              )
WHERE order_no = 'SO2026001'
RETURNING payload -> 'items' AS items;
text
                    items
----------------------------------------------
 [{"sku": "S", "qty": 3}, {"sku": "A", "qty": 1}]
(1 row)

注意这里的分工：jsonb_insert 在数组内部插元素，jsonb_set 把插入后的数组写回字段。单靠 jsonb_set 无法在数组中间插入。

第六步：用 create_if_missing = false 做「只更新已存在字段」

sql
-- 只有字段已存在时才改，避免给历史数据补上不该有的键
UPDATE orders
SET payload = jsonb_set(payload, '{discount}', '10'::jsonb, false)
WHERE order_no = 'SO2026002'
RETURNING payload;
text
                              payload
----------------------------------------------------------------------
 {"status": "paid", "amount": 88, "buyer": {"name": "alice", "note": null}}
(1 row)

discount 不存在，且 create_if_missing = false，所以什么都没变，也没报错。

第七步：用 jsonb_set_lax 处理可能为 NULL 的输入

sql
-- 模拟来自应用层的变量（可能为 NULL）
UPDATE orders
SET payload = jsonb_set_lax(
                payload,
                '{buyer,note}',
                to_jsonb(NULLIF('', '')::text),   -- 结果为 SQL NULL
                true,
                'delete_key'
              ),
    updated_at = now()
WHERE order_no = 'SO2026002'
RETURNING payload -> 'buyer' AS buyer;
text
       buyer
--------------------
 {"name": "alice"}
(1 row)

传 NULL + 'delete_key'，note 字段被移除，而不是写成 SQL NULL 或 JSON null。这是「传空即取消设置」的标准实现。

第八步：封装成函数并在 UPDATE 里复用

sql
CREATE OR REPLACE FUNCTION order_set_status(p_payload jsonb, p_status text)
RETURNS jsonb
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT jsonb_set(p_payload, '{status}', to_jsonb(p_status));
$$;

UPDATE orders
SET payload    = order_set_status(payload, 'delivered'),
    updated_at = now()
WHERE order_no IN ('SO2026001', 'SO2026002')
RETURNING order_no, payload -> 'status' AS status;
text
  order_no  |    status
------------+---------------
 SO2026001  | "delivered"
 SO2026002  | "delivered"
(2 rows)

第九步：验证修改代价

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
UPDATE orders SET payload = jsonb_set(payload, '{status}', '"done"'::jsonb) WHERE id = 1;
text
                    QUERY PLAN
---------------------------------------------------
 Update on orders (actual time=0.098..0.099 rows=0 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using orders_pkey on orders (actual time=0.021..0.023 rows=1 loops=1)
         Index Cond: (id = 1)
         Buffers: shared hit=3
 Planning Time: 0.121 ms
 Execution Time: 0.152 ms

注意 Buffers: shared hit=4——只改了一个键，但整行都要重读重写。

第十步：清理

sql
DROP FUNCTION IF EXISTS order_set_status(jsonb, text);
DROP TABLE orders;
text
DROP FUNCTION
DROP TABLE

注意事项 ​

  • 第三参必须是 jsonb。传 SQL 字符串或数字会报 function jsonb_set(jsonb, unknown, unknown) does not exist。用 'x'::jsonb 或 to_jsonb(...)。

  • SQL NULL ≠ JSON null。jsonb_set(t, p, NULL) 返回 SQL NULL，会把整列写成 NULL；要写 JSON null 必须显式 'null'::jsonb。

    sql
    SELECT
  jsonb_set('{"a": 1}'::jsonb, '{a}', NULL) IS NULL          AS sql_null_is_null,
  jsonb_set('{"a": 1}'::jsonb, '{a}', 'null'::jsonb) IS NULL AS json_null_is_null;
    text
     sql_null_is_null | json_null_is_null
------------------+------------------
 t                | f
(1 row)

  • 中间步骤不存在时静默返回原值。不报错、不影响 UPDATE 行数统计。写完请用 RETURNING 或再查一次。

  • jsonb_insert 不会覆盖已有对象键。规则是「不存在才插入」，要覆盖请用 jsonb_set。

  • insert_after 的默认值是 false，即插在目标元素之前。这条默认值最容易记反，建议在代码里显式写出布尔参。

  • 数组越界不报错，而是追加。正数追加到尾、负数追加到头。下标算错时数据会静默落到别处。

  • 路径字面量遇到特殊字符会解析错误。键名含空格、逗号、花括号时，用 ARRAY['a b', 'c,d'] 而不是 '{"a b","c,d"}'。

  • 一次只能改一条路径。多处修改必须嵌套调用，或改用 jsonb_build_object 重建整份文档。

  • 不要在一条 UPDATE 的 SET 列表里对同一列赋值两次，PostgreSQL 不保证取哪个表达式的结果，属于未定义行为。

  • 修改 jsonb 是整值重写。文档越大越慢，且会产生死元组等待 VACUUM。

  • 更新会同步维护该列上的 GIN 索引，写入代价随索引数量放大。

  • jsonb_set_lax 需要 PostgreSQL 13+。低版本只能用 COALESCE 或 CASE 手工处理 NULL。

最佳实践 ​

  • 第三参一律走 to_jsonb()，不要在应用层拼 JSON 字面量：

    sql
    jsonb_set(payload, '{nick}', to_jsonb($1::text))

    字符串里的引号、反斜杠、Unicode 转义都由 to_jsonb 负责，人工拼接几乎必然出错。

  • null_value_treatment 显式写出，即使想用默认值。'use_json_null'、'delete_key'、'return_target'、'raise_exception' 四种语义差别巨大，读代码的人不该去查默认值。

  • 路径用 ARRAY[...] 而不是字面量，尤其是路径来自变量的场合。

  • 需要「字段已存在才更新」时用 create_if_missing = false，把判断交给数据库，避免应用层先查再改引入竞态。

  • 多处修改优先用嵌套 jsonb_set，超过三层改用 jsonb_build_object 重建，可读性优先。

  • 把常用的修改逻辑封装成 IMMUTABLE 函数，既能复用，也能用于生成列与表达式索引：

    sql
    CREATE OR REPLACE FUNCTION add_tag(p_doc jsonb, p_tag text)
RETURNS jsonb LANGUAGE sql IMMUTABLE
AS $$
  SELECT CASE
           WHEN p_doc ? 'tags' THEN
             jsonb_set(p_doc, '{tags}', (p_doc -> 'tags') || to_jsonb(p_tag))
           ELSE
             jsonb_set(p_doc, '{tags}', jsonb_build_array(p_tag))
         END;
$$;

  • UPDATE 后一定用 RETURNING 复核，静默失败只有回读才能发现：

    sql
    UPDATE orders
SET payload = jsonb_set(payload, '{a,b}', '1'::jsonb)
WHERE id = 1
RETURNING payload;

  • 高频更新的字段抽成普通列。状态、计数、时间戳放在 jsonb 里，每次改动都触发整值重写，得不偿失。

  • 给 jsonb 列加 jsonb_typeof 检查约束，防止结构被意外写坏：

    sql
    CONSTRAINT ck_payload_is_object CHECK (jsonb_typeof(payload) = 'object')

  • 用带条件的 UPDATE 做乐观锁，避免并发丢更新：

    sql
    UPDATE orders
SET payload = jsonb_set(payload, '{status}', '"done"'::jsonb), updated_at = now()
WHERE id = 1 AND updated_at = $expected;
-- 影响 0 行表示版本已变，交由应用层重试

  • 修改语句先在测试库跑 EXPLAIN (ANALYZE, BUFFERS)，确认没有意外的全表扫描或索引维护开销。

下一节

继续学习：JSONPath（PostgreSQL 12+）

参考链接 ​