PostgreSQL 12如何转换jsonb类型？相同SQL在13正常12报jsonb null错

问题根因 #

PostgreSQL 13 新增了jsonb标量类型到原生数值类型的直接强制转换支持，对jsonb null值的转换做了兼容处理，会直接转为SQL层面的NULL值。但PostgreSQL 12及更早版本未支持这类直接转换，当jsonb_array_elements返回的ndp为jsonb null类型时，直接执行cast(ndp as NUMERIC)就会抛出cannot cast jsonb null错误。

解决方案 #

推荐使用jsonb_array_elements_text函数替代原有的jsonb_array_elements来处理price数组，该函数会直接将jsonb数组元素转为text类型，遇到jsonb null时会返回SQL层面的NULL，无需额外兼容逻辑即可正常转换为NUMERIC类型。

修改后的完整SQL如下：

select
    id,
    cast((nd ->> 'bath') as float) as bath,
    cast((nd ->> 'bed') as integer) as bed,
    cast(ndp as NUMERIC) as price ,
    FIRST_VALUE(cast((nd ->> 'bed') as integer)) OVER(
ORDER BY
    cast((nd ->> 'bed') as integer)) priority_bed,
    FIRST_VALUE(cast((nd ->> 'bath') as float)) OVER(
ORDER BY
    cast((nd ->> 'bath') as float)) priority_bath 
FROM
    properties p cross 
join
    lateral jsonb_array_elements(p.bed_bath_price) as nd cross 
join
    lateral jsonb_array_elements_text(nd -> 'price') as ndp

备选方案 #

如果不想修改关联逻辑，也可以直接对原jsonb类型的ndp做兼容转换，先判断是否为jsonb null再做类型转换：

-- 把原来的cast(ndp as NUMERIC)替换为下面的写法即可
cast(CASE WHEN jsonb_typeof(ndp) = 'null' THEN NULL ELSE ndp #>> '{}' END AS NUMERIC) as price

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