如何从PostgreSQL中另一张表的JSON数组列填充目标表？

嘿，这个需求我之前也处理过，PostgreSQL的数组和JSON操作函数配合起来就能轻松搞定，直接用一条INSERT...SELECT语句就能完成批量插入，我给你拆解下具体怎么做：

核心思路 #

我们需要把Foos表中baas列的JSON数组拆分成单独的JSON对象，然后提取每个对象里的字段，关联原表的id作为foo_id，最后插入到Baas表中。

完整SQL语句 #

INSERT INTO Baas (foo_id, name, property)
SELECT 
  f.id AS foo_id,
  json_obj->>'name' AS name,
  -- 如果Baas表的property是整数类型，这里做类型转换；如果是文本就去掉::integer
  (json_obj->>'property')::integer AS property
FROM Foos f,
     -- 把json数组拆分成单个JSON对象行
     unnest(f.baas) AS json_obj;

关键部分解释 #

• unnest(f.baas)：这个函数会把Foos表每行的json[]数组拆分成多行，每行对应数组里的一个JSON对象，这样我们就能逐个处理每个对象了。
• json_obj->>'name'：->>是PostgreSQL的JSON提取操作符，它会把JSON对象中指定键的值以文本形式取出来；如果用->的话会返回JSON类型，这里我们需要文本/数值类型，所以用->>。
• 类型转换：如果Baas表的property字段是整数类型，就加上::integer把提取到的文本转成整数；如果是文本类型，直接用json_obj->>'property'就行。

额外注意事项 #

1. 如果Baas表的id是自增主键（比如用SERIAL或GENERATED AS IDENTITY定义的），不需要在INSERT里指定id，PostgreSQL会自动生成。
2. 如果你的JSON数组里可能存在空元素或者非对象类型的元素，可以加个过滤条件避免出错：

INSERT INTO Baas (foo_id, name, property)
SELECT 
  f.id AS foo_id,
  json_obj->>'name' AS name,
  (json_obj->>'property')::integer AS property
FROM Foos f,
     unnest(f.baas) AS json_obj
WHERE json_obj IS NOT NULL 
  AND json_typeof(json_obj) = 'object'; -- 确保处理的是有效的JSON对象

备注：内容来源于stack exchange，提问作者JakesMD