PostgreSQL 用 jsonb_to_recordset 批量更新：JSON 数组展开与 JOIN

问题场景

应用层收到一批用户变更，形如 JSON 数组。逐行 UPDATE 会产生多次数据库往返；把数组拼成超长 VALUES 又难维护。PostgreSQL 的 jsonb_to_recordset 可把 JSON 数组按列定义展开成行集，写入临时表后与主表 JOIN，一次完成批量更新。

示例表

CREATE TABLE IF NOT EXISTS app_user (
  id bigint PRIMARY KEY,
  display_name text NOT NULL,
  status text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO app_user (id, display_name, status)
VALUES
  (1001, '旧名称A', 'active'),
  (1002, '旧名称B', 'active')
ON CONFLICT (id) DO NOTHING;

展开 JSON 数组并更新

jsonb_to_recordset 需要列定义列表，列名与 JSON 对象键对应，类型要能承接 JSON 值。下面脚本在事务内建临时表，主键用于拒绝重复 id：

BEGIN;

CREATE TEMP TABLE stage_user (
  id bigint PRIMARY KEY,
  display_name text NOT NULL,
  status text NOT NULL
) ON COMMIT DROP;

INSERT INTO stage_user (id, display_name, status)
SELECT id, display_name, status
FROM jsonb_to_recordset(
  $json$[
    {"id": 1001, "display_name": "新名称A", "status": "disabled"},
    {"id": 1002, "display_name": "新名称B", "status": "active"}
  ]$json$::jsonb
) AS x(id bigint, display_name text, status text);

UPDATE app_user AS u
SET display_name = s.display_name,
    status = s.status,
    updated_at = now()
FROM stage_user AS s
WHERE u.id = s.id;

COMMIT;

SELECT id, display_name, status
FROM app_user
WHERE id IN (1001, 1002)
ORDER BY id;

在应用里将字面量替换为参数：jsonb_to_recordset($1::jsonb) AS x(...)，把 JSON 文本作为参数传入。临时表只承载本次批次，ON COMMIT DROP 在提交后清理。

运行方法

将脚本保存为 batch_update.sql，用 psql 执行：

psql -h <host> -U <user> -d <dbname> -f batch_update.sql

先执行建表与示例数据，再执行事务块；最后查询应看到 1001、1002 的名称和状态被更新。重复执行时，示例数据的 ON CONFLICT DO NOTHING 不会重置主表，更新仍会生效。

边界与限制

• 空数组：jsonb_to_recordset('[]'::jsonb) 返回 0 行，INSERT 和 UPDATE 都不影响数据。参数可能为 NULL 时，可先 COALESCE($1::jsonb, '[]'::jsonb)。
• 非数组：函数要求 JSON 数组，传入对象会报错；建议在应用层校验 Array.isArray(payload)，或用 jsonb_typeof(...) = 'array' 做前置判断。
• 重复 id：临时表主键会使重复键报错并回滚，这是可预期行为；若业务允许覆盖，应先明确去重规则，不要依赖 UPDATE 的不确定匹配。
• NULL 语义：缺失键和 JSON null 在 jsonb_to_recordset 中都会变成 SQL NULL。上面的直接赋值适合“整行字段必填”的语义；若字段可空且要区分“未传”与“显式置空”，需要额外展开标志位或使用 jsonb_array_elements 配合 ? 判断，不能只用 COALESCE 推断。
• 类型与事务：JSON 数字、字符串、布尔值要能转换为列定义类型，转换失败会中断事务。批量更新应在事务中执行，确认行数后再提交。
• 额外键会被忽略；数组元素不是对象时会报错。更新前建议校验目标 id 是否存在，必要时先查缺失或多余 id。

小结

用 jsonb_to_recordset 把 JSON 数组展开为多列行集，再通过临时表与主表 JOIN，可以把批量更新写成一条集合操作 SQL。关键是列定义与 JSON 键一致、用主键约束处理重复 id，并明确空数组和 NULL 的业务语义。