拆分JSON数组列更新i0到i23共24个integer列的SQL实现方法

错误原因 #

报错的核心原因是PostgreSQL中->运算符提取JSON数组元素时返回的是JSON类型值，无法直接强制转换为整数，需要先使用->>运算符将元素以文本格式取出后再做整数转换。

正确SQL写法 #

如果你的jsoncol本身就是json/jsonb类型，语句如下：

UPDATE 你的实际表名
SET 
i0 = (jsoncol ->> 0)::INTEGER,
i1 = (jsoncol ->> 1)::INTEGER,
i2 = (jsoncol ->> 2)::INTEGER,
-- 按上述规则依次补全i3到i22的赋值逻辑
i23 = (jsoncol ->> 23)::INTEGER;

如果你的jsoncol是文本类型存储的JSON字符串，需要先转换为JSON类型再取值：

UPDATE 你的实际表名
SET 
i0 = (jsoncol::json ->> 0)::INTEGER,
i1 = (jsoncol::json ->> 1)::INTEGER,
-- 按上述规则依次补全i2到i22的赋值逻辑
i23 = (jsoncol::json ->> 23)::INTEGER;

注意：不要直接使用SQL保留字table作为表名，请替换为你自己的业务表名

可选优化（避免转换失败） #

如果你使用的是PostgreSQL 12及以上版本，可以用TRY_CAST替换强制类型转换，当JSON值不符合整数格式时会自动赋值为NULL，不会导致整个更新语句报错终止：

i0 = TRY_CAST(jsoncol ->> 0 AS INTEGER)

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