问题:如果你存在数据库中的某个字段的值为varchar格式,不过是json标准字符串格式,比如:{"zLen":"2438.4","yLen":"15049.9996086053","xLen":"26101.272018522"}
, 那么,在查询的时候就可以转换为Jsonb格式。
解决:
1、首先查看是否支持json格式:
SELECT '""'::json;
如果结果出现json,那么就表示OK
2、查询并转换语句:
SELECT cast(bounding_box as jsonb) ->> 'xLen' as xLen,
cast(bounding_box as jsonb) ->> 'yLen' as yLen,
cast(bounding_box as jsonb) ->> 'zLen' as zLen
from table01;
结果为:
但此时是字符串格式,如果需要进行运算,可以这样写:
SELECT to_number(cast(bounding_box as jsonb) ->> 'xLen', '99999D9999999999') as xLen ,
to_number( cast(bounding_box as jsonb) ->> 'yLen', '99999D9999999999' )as yLen
from table01 ;
结果为:
接下来就可以进行其他运算操作了!