如何将PostgreSQL行键与jsonb数组的每个值关联

完全可以用SQL直接实现这个需求，而且效率远高于应用程序处理3000万条数据的方式。

问题根源 #

你之前的SQL语句出错是因为表名和字段名不匹配：原表是staging_book，但你写的是stagework；原表主键是book_id，示例中用的work_id大概率是笔误。这才导致出现所有作者与所有作品错误关联的情况。

正确的拆分SQL #

SELECT s.book_id AS work_id, a.author
FROM staging_book s
CROSS JOIN LATERAL jsonb_array_elements_text(s.authors) AS a(author);

CROSS JOIN LATERAL的作用是：为原表的每一行数据，将对应的authors JSONB数组拆分成独立的行，同时保留原行的book_id（或work_id），完美实现你要的关联效果。

错误数据处理建议 #

在拆分前可以先过滤或修正错误数据，避免报错或生成无效记录：

• 过滤authors为NULL的行：WHERE s.authors IS NOT NULL
• 确保authors是数组类型：WHERE jsonb_typeof(s.authors) = 'array'
• 过滤空数组：WHERE jsonb_array_length(s.authors) > 0

整合错误处理的完整SQL：

SELECT s.book_id AS work_id, a.author
FROM staging_book s
CROSS JOIN LATERAL jsonb_array_elements_text(s.authors) AS a(author)
WHERE s.authors IS NOT NULL
  AND jsonb_typeof(s.authors) = 'array'
  AND jsonb_array_length(s.authors) > 0;

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