如何将JSON数组解析为PostgreSQL的TEXT[]数组类型？

你遇到的问题本质是JSON数组的字符串格式和PostgreSQL原生数组的语法不兼容，直接强制转换自然会报错。替换括号的方式虽然能解决简单场景，但存在边缘case风险（比如元素内容包含[/]时会出错），PostgreSQL提供了原生函数来优雅解决这个问题，以下是两种可靠方案：

方案1：使用数组元素展开+聚合（兼容所有PostgreSQL版本） #

如果你的JSON字段是json类型，用json_array_elements_text展开数组元素，再用array_agg聚合为TEXT[]：

SELECT array_agg(elem)::text[] AS problems
FROM your_table t, json_array_elements_text(t.items->'problems') elem;

如果是jsonb类型（推荐使用的JSON存储类型），替换为jsonb_array_elements_text即可：

SELECT array_agg(elem)::text[] AS problems
FROM your_table t, jsonb_array_elements_text(t.items->'problems') elem;

方案2：使用原生转换函数（PostgreSQL 12+） #

PostgreSQL 12及以上版本新增了直接转换的函数，写法更简洁：

• 针对json类型：

SELECT json_array_to_text_array(t.items->'problems') AS problems
FROM your_table t;

• 针对jsonb类型：

SELECT jsonb_array_to_text_array(t.items->'problems') AS problems
FROM your_table t;

实际插入示例 #

结合你的表X，可以直接用这些函数完成数据插入：

INSERT INTO X(problems)
SELECT jsonb_array_to_text_array(t.items->'problems')
FROM your_source_table t;

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