PostgreSQL中如何将JSONB类型数据转换为数组类型

假设你的products表中有一个名为test的JSONB列，存储的是类似["A", "B", "C"]的字符串数组，需要将其转换为VARCHAR(2)[]类型，以下是修正后的完整SQL脚本：

-- 添加临时数组列
ALTER TABLE "products" ADD COLUMN "test_array" VARCHAR(2)[];

-- 将JSONB数组转换为目标数组类型
UPDATE "products" 
SET test_array = array(SELECT jsonb_array_elements_text(test))::VARCHAR(2)[]
-- 可选：过滤非数组格式的JSONB数据，避免转换报错
WHERE jsonb_typeof(test) = 'array';

-- 删除原JSONB列，并重命名临时列
ALTER TABLE "products" DROP COLUMN "test";
ALTER TABLE "products" RENAME COLUMN "test_array" TO "test";

关键说明 #

• jsonb_array_elements_text(test)：将JSONB数组拆分为单个文本值的行集合
• array(...)：将行集合重新组合为PostgreSQL数组
• ::VARCHAR(2)[]：强制转换为指定长度的字符数组类型
• 可选的WHERE条件：确保只处理合法的JSONB数组，避免因列中存在非数组JSONB数据导致更新失败

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