pgsql实现json格式转换

1.需要转换的json数据，json对象嵌套json数组

{
        "recordMethod1": {
                "method": "1",
                "methodDisplay": "1",
                "convertFormula": "",
                "valueTitle": "",
                "points": [{
                        "minValue": "12",
                        "maxValue": "23"
                }, {
                        "minValue": "22",
                        "maxValue": "24"
                }]
        },
        "recordMethod2": {
                "method": "2",
                "methodDisplay": "2",
                "convertFormula": "3",
                "valueTitle": "",
                "points": [{
                        "minValue": "12",
                        "maxValue": "23"
                }, {
                        "minValue": "22",
                        "maxValue": "24"
                }]
        }
}

        这是我进行操作的表结构如下：

表名：record_speed
record_method_detail	text	集合列表
record_id	varchar	唯一标识

2.首先对json对象进行解析转换

（对json对象进行查询）对recordMethod1和recordMethod2使用别名来查询显示

select 
record_method_detail::json -> 'recordMethod1' as method1,
record_method_detail::json -> 'recordMethod2' as method2,
from record_speed
where record_id='01' and record_method_detail <> ''

查询结果如下：查询出来的是对象形式

（查询json对象对应的属性转换）对某个对象的属性进行分属性查询数据

select 
record_method_detail::json -> 'recordMethod1'->> 'method' as method,
record_method_detail::json -> 'recordMethod1'->> 'methodDisplay'as methodDisplay,
record_method_detail::json -> 'recordMethod1'->> 'convertFormula' as convertFormula,
record_method_detail::json -> 'recordMethod1'->> 'valueTitle'as valueTitle
from record_speed
where record_id='01' and record_method_detail <> ''

 查询结果：recordMethod1的json对象属性

（查询recordMethod1json对象的points的json数组）把recordMethod1对象里面的“points”数组对象使用jsonb_array_elements解析成数组来查询

select 
two->> 'minValue' as minValue,
two->> 'maxValue' as maxValue
from record_speed,jsonb_array_elements(
(select 
(record_method_detail::json->'recordMethod1')->>'points' as method1 
from record_speed where record_id='12')::jsonb) two
where record_id='12' and record_method_detail <> ''

-- 要校验该属性字段是否非空，如果为空进行转换会报空指针错误
--jsonb_array_elements 解析json数组转成多行