PostgreSQL中如何从JSON数组列批量插入数据至新表？

数据老陈

2026-8-18

解决PostgreSQL JSON数组拆分行并关联原表字段插入的问题

要实现遍历task表每行数据，同时拆分customFields中的JSON数组元素，将每个元素映射插入form_field_value表，核心是用LATERAL JOIN关联原表和数组拆分后的结果，确保原表的form、workspace字段能和每个数组元素对应。

正确的SQL写法

-- 先验证查询结果，确认数据正确后再执行INSERT
SELECT
  cf ->> 'name' AS "name",
  cf ->> 'label' AS "label",
  cf ->> 'type' AS "inputType",
  cf ->> 'options' AS "options",
  cf ->> 'value' AS "value",
  task.form,
  task.workspace
FROM task
-- 用LATERAL JOIN拆分数组，每个数组元素对应原表的一行数据
LATERAL JOIN jsonb_array_elements(task."customFields") AS t(cf)
-- 可选：过滤掉customFields为NULL的行，避免无效数据
WHERE task."customFields" IS NOT NULL;

-- 确认结果正确后执行插入
INSERT INTO form_field_value ("name", "label", "inputType", "options", "value", "form", "workspace")
SELECT
  cf ->> 'name',
  cf ->> 'label',
  cf ->> 'type',
  cf ->> 'options',
  cf ->> 'value',
  task.form,
  task.workspace
FROM task
LATERAL JOIN jsonb_array_elements(task."customFields") AS t(cf)
WHERE task."customFields" IS NOT NULL;

关键说明

LATERAL JOIN的作用：
它允许在JOIN子句中引用主表（task）的列，这样每个task行的customFields数组都会被拆分成独立的行，每一行对应一个数组元素，同时保留原task行的form、workspace等字段，完美解决之前无法关联原表字段的问题。
JSON类型适配：
如果customFields是json类型而非jsonb，将jsonb_array_elements替换为json_array_elements即可。
数据验证：
建议先单独执行SELECT语句，检查拆分后的字段映射、关联是否正确，确认无误后再执行INSERT，避免数据错误。