取json中字段属性值 转成table

json格式

{"items":[{"key":"品牌","value":"格兰仕"},{"key":"型号","value":"5匹"},{"key":"额定功率","value":"14W"},{"key":"寿命","value":"12"},{"key":"市场价格","value":"2200"},{"key":"出场时间","value":""},{"key":"采购时间","value":"11/12/21"},{"key":"安装时间","value":"11/15/21"},{"key":"项目信息","value":"重庆"},{"key":"系统名称","value":"空调系统"},{"key":"资产编号","value":"CL.C.0007"},{"key":"资产名称","value":"立式空调"},{"key":"数量","value":"1"},{"key":"购买时间","value":"44501"},{"key":"使用月限","value":""},{"key":"状态","value":"使用中"},{"key":"地址","value":"实训室"}]}

查询取出items中json

查询取出items中json

SELECT  json_array_elements((etd_data::json->>'items')::json)  FROM  t_extra_table_data t where etd_data_item_id='0115668e-0305-40e5-8a35-60d97b911050'

获取结果
{"key":"品牌","value":"格兰仕"}
{"key":"型号","value":"5匹"}
{"key":"额定功率","value":"14W"}
{"key":"寿命","value":"12"}
{"key":"市场价格","value":"2200"}
{"key":"出场时间","value":""}
{"key":"采购时间","value":"11/12/21"}
{"key":"安装时间","value":"11/15/21"}
{"key":"项目信息","value":"重庆"}
{"key":"系统名称","value":"空调系统"}
{"key":"资产编号","value":"CL.C.0007"}
{"key":"资产名称","value":"立式空调"}
{"key":"数量","value":"1"}
{"key":"购买时间","value":"44501"}
{"key":"使用月限","value":""}
{"key":"状态","value":"使用中"}
{"key":"地址","value":"实训室"}

取出所有key 和value

SELECT json_array_elements((etd_data::json->>'items')::json)::json->>'key' as key,json_array_elements((etd_data::json->>'items')::json)::json->>'value' as value FROM  t_extra_table_data t where etd_data_item_id='0115668e-0305-40e5-8a35-60d97b911050'
key     value
品牌	格兰仕
型号	5匹
额定功率	14W
寿命	12
市场价格	2200
出场时间	
采购时间	11/12/21
安装时间	11/15/21
项目信息	重庆
系统名称	空调系统
资产编号	CL.C.0007
资产名称	立式空调
数量	1
购买时间	44501
使用月限	
状态	使用中
地址	实训室