PostgreSQL / 系统课时

更新 JSONB 局部字段

更新 JSONB 局部字段

JSONB 支持在 SQL 中直接修改指定路径、插入数组元素或删除成员,避免应用先读出整份文档再拼装。本节用一条商品记录串起常见局部更新,并说明这些操作改变的是更新表达方式,不代表 PostgreSQL 会在存储层原地改几个字节。

学习目标

  • 使用 jsonb_set、jsonb_insert 和删除运算符修改指定路径
  • 区分路径创建、数组插入和嵌套键删除的边界
  • 理解 JSONB 局部修改仍然属于一次 PostgreSQL 行版本更新

用一个主示例完成局部更新

下面创建一条商品记录,先用 jsonb_set 修改 dimensions.width,再用 jsonb_insert 向 tags 追加元素,最后用 #- 删除 dimensions.height。jsonb_set 的路径参数是文本数组,写入值必须是 JSONB;jsonb_insert 的最后一个布尔参数表示是否插在目标元素之后。

sql
DROP TABLE IF EXISTS product_catalog;

CREATE TABLE product_catalog (
    id bigint PRIMARY KEY,
    attributes jsonb NOT NULL,
    version integer NOT NULL DEFAULT 1
);

INSERT INTO product_catalog (id, attributes)
VALUES (
    1,
    '{"color":"red","dimensions":{"width":20,"height":30},"tags":["sale"]}'::jsonb
);

UPDATE product_catalog
SET attributes = jsonb_set(
    attributes,
    '{dimensions,width}',
    '25'::jsonb
)
WHERE id = 1;

-- 先验证 jsonb_set:查询必须从 product_catalog 读取目标列。
SELECT attributes #>> '{dimensions,width}' AS width
FROM product_catalog
WHERE id = 1;

UPDATE product_catalog
SET attributes = jsonb_insert(
    attributes,
    '{tags,0}',
    '"featured"'::jsonb,
    true
)
WHERE id = 1;

UPDATE product_catalog
SET attributes = attributes #- '{dimensions,height}'
WHERE id = 1;

SELECT
    attributes #>> '{dimensions,width}' AS width,
    attributes -> 'tags' AS tags,
    attributes #> '{dimensions,height}' IS NULL AS height_removed
FROM product_catalog
WHERE id = 1;

预期结果:第一次查询应得到 25;三个 UPDATE 都应影响 1 行,最后一次查询应得到以下结果。

text
 width
-------
 25
(1 row)

 width |          tags          | height_removed
-------+------------------------+----------------
 25    | ["sale", "featured"] | t
(1 row)

这里验证的是路径值和删除结果,而不是 JSONB 对象键的显示顺序。对象键排列可能变化,不能把序列化后的键顺序当作业务语义。

路径不存在时的边界

jsonb_set 默认允许创建最后一级成员,但前面的路径必须已经存在。例如 {dimensions,depth} 可以在现有 dimensions 对象中增加 depth;如果 dimensions 本身不存在,它不会自动创建整套中间对象。

对象顶层键可用 attributes - 'color' 删除;嵌套路径更适合用 #-。应让 SQL 直接表达目标位置,而不是把 JSONB 转成文本后重新拼接。

局部语义不等于原地修改

SQL 看起来只改了一个路径,但 PostgreSQL 的 UPDATE 仍遵循 MVCC:旧行版本要继续服务仍需读取它的事务,同时生成新的行版本。即使只改 JSONB 中一个很小的值,也可能增加 WAL,并对较大的 TOAST 数据造成额外写入;即便更新能够采用 HOT,也不等于在 JSONB 值内部原地修改几个字节。

JSONB 局部修改与物理行更新的区别

因此,高频变化的计数器、状态或时间戳若经常独立更新,通常更适合拆成普通列;JSONB 更适合承载结构灵活、更新频率相对低的属性集合。

用版本条件避免静默覆盖

局部更新函数不会自动解决并发覆盖。若请求携带业务版本号,可以把“修改 JSONB”和“版本递增”放进同一条 UPDATE,让过期请求自然更新 0 行。为了单独演示这个条件,前面的三次更新没有修改 version,所以此时仍为 1;实际应用应约定所有受版本保护的写入都递增版本号。

sql
UPDATE product_catalog
SET attributes = jsonb_set(
        attributes,
        '{color}',
        '"blue"'::jsonb
    ),
    version = version + 1
WHERE id = 1
  AND version = 1
RETURNING version, attributes ->> 'color' AS color;

预期结果

text
 version | color
---------+-------
       2 | blue
(1 row)

UPDATE 1

如果另一请求仍使用旧版本号 1,相同条件将不再匹配,应用即可把受影响行数为 0 识别为并发冲突。JSONB 局部函数能减少应用侧文档拆装,但是否继续把字段放在同一个 JSONB 中,仍应结合文档大小、更新频率和并发模型判断。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

更新 JSONB 局部字段

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通