PostgreSQL中如何将jsonb列数据转换为自定义类型并拆分为多列输出

你要的效果可以通过PostgreSQL的复合类型展开语法实现，写法如下：

SELECT (jsonb_populate_record(null::my_type, body)).*
FROM reviews_ingestion;

实现说明 #

你之前的写法返回的是my_type类型的复合值，PostgreSQL默认会将其作为单列输出，在表达式外层加括号后接.*，就可以把复合类型的所有字段自动展开为独立列，字段类型会完全沿用你定义my_type时指定的类型，不需要手动做类型转换，也不需要逐个写字段提取逻辑。

如果需要直接写入规范结构的目标表，可以直接用以下语句：

INSERT INTO 目标表名 (id, type, section_id, 其他对应字段)
SELECT (jsonb_populate_record(null::my_type, body)).*
FROM reviews_ingestion;

注意事项 #

需要保证jsonb数据中的键名和my_type的字段名完全匹配，否则未匹配到的字段会返回null值。这个方案是逐行处理，不需要做全量数据聚合，性能和直接提取字段的写法基本一致。

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