PostgreSQL用关联表含空值字段更新JSONB列报错排查

1. 触发错误的直接原因：键的引号使用错误 #

PostgreSQL中，双引号用于引用标识符（如表名、列名），而json_build_object要求键为字符串字面量，必须用单引号包裹。你代码里的"key1"这类写法会被数据库解析为列名，若当前上下文不存在对应列，就会返回NULL，这直接触发了argument 7 cannot be null错误（对应第7个参数"key7"解析为null），同时触发提示“Object keys should be text.”。

修正后的SQL代码：

UPDATE A_table a
 SET new_json_col = json_build_object(
   'key1', b.col1,
   'key2', b.col2,
   'key3', b.col3,
   'key4', b.col4,
   'key5', b.col5,
   'key6', b.col6,
   'key7', b.col7,
   'key8', b.col8
   )
 FROM B_table b
 WHERE a.id = b.a_id;

2. 一对多关联的潜在问题 #

由于A与B是一对多关系，单个A对应多个B行，使用UPDATE ... FROM时，数据库会随机选取匹配的某一行更新A的列，最终每个A仅会保留最后匹配到的单个B的JSON数据，而非所有关联B的数据。若你需要将所有关联的B行打包为JSON数组，应改用json_agg结合子查询：

UPDATE A_table a
 SET new_json_col = (
   SELECT json_agg(
      json_build_object(
         'key1', col1,
         'key2', col2,
         'key3', col3,
         'key4', col4,
         'key5', col5,
         'key6', col6,
         'key7', col7,
         'key8', col8
      )
   )
   FROM B_table b
   WHERE b.a_id = a.id
 );

3. TypeORM迁移的注意事项 #

在TypeORM迁移脚本中执行SQL时，需注意字符串转义问题：

• 若使用queryRunner.query()执行原生SQL，单引号无需额外转义（若用模板字符串包裹，需避免转义冲突）；
• 也可通过TypeORM查询构建器构建更新语句，减少手动写SQL的语法错误，示例如下：

await queryRunner.manager
  .createQueryBuilder()
  .update(A_table)
  .set({
    new_json_col: (qb) => qb
      .select("json_agg(json_build_object('key1', b.col1, 'key2', b.col2, 'key3', b.col3, 'key4', b.col4, 'key5', b.col5, 'key6', b.col6, 'key7', b.col7, 'key8', b.col8))")
      .from(B_table, 'b')
      .where('b.a_id = A_table.id')
  })
  .execute();

内容的提问来源于stack exchange，提问作者Jun