PostgreSQL 15.4中如何将text[]类型列转换为jsonb[]类型？

步骤1：排查无效JSON数据 #

首先要确认原text[]列里的每个字符串都是合法JSON，避免转换失败：

-- 找出包含无效JSON的行及对应内容
SELECT
  id, -- 替换为表的主键/唯一标识列
  x AS invalid_json_text
FROM certificate, unnest(criterias) AS x
WHERE NOT pg_is_json(x);

如果查询返回结果，先修复这些无效JSON（比如补全引号、修正语法），再进行下一步。

步骤2：执行列类型转换 #

确认所有JSON合法后，直接用ALTER TABLE完成类型转换：

ALTER TABLE certificate
ALTER COLUMN criterias TYPE jsonb[]
USING array(SELECT jsonb(x) FROM unnest(criterias) AS x);

这条语句会把原text[]数组的每个元素解析为jsonb，再重新组合成jsonb[]数组，直接替换原列的类型和数据。

验证转换结果 #

转换完成后，可查询验证数据是否正确：

SELECT
  id,
  criterias,
  jsonb_typeof(criterias[1]) -- 检查第一个元素类型，正常应返回"object"
FROM certificate
LIMIT 5;

内容的提问来源于stack exchange，提问作者Ahmet yazıcı