如何将PostgreSQL无键的简单JSON值列转换为int类型

现有mytable表，包含id（int类型）和val（json类型）字段，val存储带引号的JSON标量值（如"null"、"0"、"2"），需要将val转换为整数类型。

最优实现语句 #

SELECT id, nullif(val #>> '{}', 'null')::int AS val_int FROM mytable;

若使用jsonb类型（PostgreSQL 9.4+支持），也可写成：

SELECT id, nullif(val::jsonb::text, 'null')::int AS val_int FROM mytable;

方案解析 #

• val #>> '{}'：PostgreSQL原生JSON函数，用于提取JSON标量的文本内容，无需指定键名，直接得到去掉引号的原始字符串。
• nullif(..., 'null')：将JSON字符串"null"转换为SQL的NULL，避免转换整数时触发错误。
• ::int：将处理后的字符串转换为整数类型。

对比原方案的优势 #

原方案通过字符串截取去除引号，属于纯文本操作；而此方案利用JSON原生函数，更适配JSON类型的操作逻辑，性能更优，代码可读性更强。

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