子查询更新jsonb字段必须用set配合jsonb_set()，不能直接赋值；子查询须返回单个标量值，路径中数字下标需转text，新值需显式::jsonb，空值会导致键被删除。
子查询更新 JSONB 字段必须用 SET 配合 jsonb_set()，不能直接赋值
PostgreSQL 不允许在 UPDATE 中对 jsonb 字段直接使用子查询结果做“整体替换”，比如 SET data = (SELECT ...) 会报错“subquery must return only one column”。真正可行的方式是把子查询作为 jsonb_set() 的某个参数（通常是新值或路径），或者先用子查询构造出完整的新 JSONB 值再塞进去。
常见错误现象：ERROR: more than one row returned by a subquery used as an expression 或 column "data" is of type jsonb but expression is of type record，本质是类型不匹配或返回多行。
-
jsonb_set()是唯一安全、可控的更新嵌套字段方式，它接受路径数组（如{user,name}）、新值、是否创建缺失路径（create_missing）三个关键参数 - 子查询必须返回单个标量值（如
text、jsonb），不能返回多列或多行；若需拼接多个字段，用ROW_TO_JSON()或JSONB_BUILD_OBJECT()封装 - 路径中含数字下标（如数组第 0 项）要写成字符串
"0"，不是整数0，否则报错malformed array literal
嵌套对象更新：用子查询生成新值 + jsonb_set() 定位路径
比如想把 orders 数组里 status = 'pending' 的第一条记录的 updated_at 改为当前时间，并打上 "processed_by": "batch_job" 标签。不能靠模糊匹配，得先定位索引——这必须靠子查询。
UPDATE orders_table
SET data = jsonb_set(
data,
ARRAY['orders', (SELECT idx::text FROM (
SELECT ROW_NUMBER() OVER () - 1 AS idx, value
FROM jsonb_array_elements(data->'orders') AS value
WHERE (value->>'status') = 'pending'
LIMIT 1
) t)::text, 'processed_by'],
'"batch_job"',
true
)
WHERE id = 123;
这里的关键点是：子查询必须返回单个字符串（idx::text），用于拼出完整路径 ARRAY['orders', '0', 'processed_by']；true 表示自动创建中间缺失键（比如 processed_by 不存在就新建）。
- 路径数组中每个元素都必须是
text类型，数字下标务必转字符串，(SELECT 0)::text不行，得写(SELECT '0')或强转 - 如果子查询没找到匹配项，整个
jsonb_set()返回原值，不会报错——这是安全行为，但你要意识到更新可能“静默失败” - 性能敏感场景慎用
jsonb_array_elements()，它会强制展开整个数组；若数组很大且匹配项靠后，考虑先用data @> '{"status":"pending"}'做索引扫描预过滤
数组元素条件更新：避免多次扫描，用 jsonb_path_query_array() 提前提取
当需要批量更新数组中满足某条件的所有元素（不止第一个），反复调用 jsonb_array_elements() 效率低且逻辑难控。PostgreSQL 12+ 提供的 jsonb_path_query_array() 可一次性提取所有匹配路径，再配合 jsonb_set() 循环更新——但注意：SQL 里没法真“循环”，得靠递归 CTE 或应用层处理。
更务实的做法是：用子查询生成一个完整的新数组，再整体替换旧字段。例如，把所有 items 中 price 的条目 <code>discounted 设为 true：
UPDATE products
SET data = (
SELECT jsonb_set(
data,
'{items}',
COALESCE(
(SELECT jsonb_agg(
CASE WHEN (item->>'price')::numeric 'items') AS item),
'[]'::jsonb
)
)
)
WHERE id = 456;
-
jsonb_agg()必须搭配FROM jsonb_array_elements()，不能直接对子查询结果聚合；漏掉FROM会报错set-returning functions are not allowed in -
COALESCE(..., '[]'::jsonb)防止原items为空或 null 导致整个表达式为 null - 这种“全量重建数组”方式比逐个路径更新更稳定，尤其适合条件较复杂、匹配数量不确定的场景
容易忽略的类型陷阱：子查询返回 jsonb 时别漏掉 ::jsonb
子查询如果返回的是 record 或 text，直接喂给 jsonb_set() 的第三个参数（新值）会出错。最典型的是用 ROW_TO_JSON() 或 TO_JSONB() 构造值，但忘了显式转类型。
错误写法：jsonb_set(data, '{meta}', (SELECT row_to_json(t) FROM (SELECT now() as ts, 'v2' as ver) t)) → 报错 cannot cast type record to jsonb。
正确写法必须加类型转换：
jsonb_set(
data,
'{meta}',
(SELECT TO_JSONB(t) FROM (SELECT NOW() AS ts, 'v2' AS ver) t)::jsonb,
true
)
-
TO_JSONB()比ROW_TO_JSON()更安全，它直接返回jsonb类型，无需额外转换；ROW_TO_JSON()返回json，还得::jsonb - 字符串字面量如
'"hello"'是 text，要当 jsonb 值用就得写成'"hello"'::jsonb，否则会被当成普通字符串插入，导致外层多一层引号 - 空值处理：子查询可能返回
NULL，而jsonb_set()第三个参数为NULL时会删掉对应 key——这不是 bug，是设计行为，要小心
实际操作中，最复杂的往往不是语法，而是路径动态拼接和空值边界。一旦子查询返回空、类型错、路径越界，PostgreSQL 就静默跳过或报晦涩错误，建议每次写完先用 SELECT 单独验证子查询结果是否符合预期。
