PostgreSQL：将jsonb数据迁移至非JSONB结构的新表

你的原查询存在一个隐患：多次调用jsonb_array_elements会导致数组被重复展开，可能生成意外的笛卡尔积结果。正确的做法是使用LATERAL JOIN一次性展开attachments数组，让每个数组元素与原表的doc_id正确关联。

验证数据提取的查询语句 #

先确认数据提取结果是否符合预期：

SELECT 
    b.doc_id,
    att ->> 'size' AS size,
    COALESCE(att ->> 'type', '') AS type, -- 处理type字段不存在的情况，转为空字符串
    att ->> 'storageId' AS storage_id
FROM _book b
LEFT JOIN LATERAL jsonb_array_elements(b.data -> 'attachments') att ON true
WHERE jsonb_array_length(b.data -> 'attachments') > 0 -- 过滤无附件的行
ORDER BY b.created_at;

直接迁移数据到_doc表 #

使用INSERT INTO ... SELECT语法，将查询结果直接写入目标表：

INSERT INTO _doc (doc_id, size, type, storage_id)
SELECT 
    b.doc_id,
    (att ->> 'size')::INT AS size, -- 若目标表size为数值类型，需转换字符串为整数
    COALESCE(att ->> 'type', '') AS type,
    att ->> 'storageId' AS storage_id
FROM _book b
LEFT JOIN LATERAL jsonb_array_elements(b.data -> 'attachments') att ON true
WHERE jsonb_array_length(b.data -> 'attachments') > 0;

关键细节说明 #

• LATERAL JOIN是核心：确保每个attachments元素都能和原表的doc_id一一绑定，避免重复展开导致的数据错误。
• COALESCE处理缺失字段：针对数组元素中type字段不存在的场景，将其转为空字符串，匹配目标表的预期格式。
• 类型转换：如果目标表的size字段定义为整数，必须用::INT将JSON中的字符串值转为数值类型；若为字符串类型则可省略转换。

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