postgresql 函数运用

json_build_object 函数构建响应对象

select json_build_object('hospdevice', hospdevice)
from(
select json_agg(row_to_json(t)) as hospdevice from 
(select hospcode, json_agg(json_build_object(devmode, devmode, 'devsn', devsn)) as device
from aimb.t_mb_hosp_device 

group by hospcode
)t
) a
- [x] 
批量插入操作

insert into aimb.t_mb_hosp_contract_device as c (
hospcode,
ssid,
contractserialid,
devtype,
devtypename,
devmanuid,
devmanuname,
devmode,
devsn,
devmemo,
createdtime,
modifiedtime
)
select contract_id, json_extract_path_text(devinfo, 'devtype') as devtype,to_timestamp(json_extract_path_text(devinfo, 'createdtime'),'yyyy-MM-dd HH24:mi:ss') as createdtime from (
select 'c111' as contract_id, json_array_elements_text('[{"devtype": "1", "devtypename": "1", "devmanuid": "1", "devmanuname": "1", "devmode": "1", "devsn": "1", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}, {"devtype": "1", "devtypename": "1", "devmanuid": "1", "devmanuname": "1", "devmode": "1", "devsn": "1", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}]')::json as devinfo
) t

批量更新

update aimb.t_mb_hosp_device SET usestatus = '1' where ('[{"devsn":"' || devsn || '", "devmode":"'|| devmode ||'" , "devmanuid":"'|| devmanuid ||'" ,"devtype":"'|| devtype ||'"}]')::jsonb  <@ 

(select array_to_json(array_agg(row_to_json(t1)))::jsonb as devsn from (
select 
json_extract_path_text(devinfo, 'devsn') as devsn ,
json_extract_path_text(devinfo, 'devtype') as devtype,
json_extract_path_text(devinfo, 'devmanuid') as devmanuid,
json_extract_path_text(devinfo, 'devmode') as devmode 
from (
select json_array_elements_text('[{"devtype": "00000", "devtypename": "1", "devmanuid": "00001", "devmanuname": "1", "devmode": "Z3518A", "devsn": "12", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}, {"devtype": "00000", "devtypename": "1", "devmanuid": "00001", "devmanuname": "1", "devmode": "Z3518A", "devsn": "13", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}]')::json  as devinfo
) t ) t1 ) 

上面这个语句地方

这个地方一定不能在进行 json 转换，否则报错

update aimb.t_mb_hosp_device SET usestatus = '1' where ('[{"devsn":' || devsn || '}]')::jsonb <@ (select array_to_json(array_agg(row_to_json(t1)))::jsonb as devsn from (

select
json_extract_path_text(devinfo, 'devsn')::json as devsn from (
select json_array_elements_text('[{"devtype": "1", "devtypename": "1", "devmanuid": "1", "devmanuname": "1", "devmode": "1", "devsn": "12", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}, {"devtype": "1", "devtypename": "1", "devmanuid": "1", "devmanuname": "1", "devmode": "1", "devsn": "13", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}]')::json as devinfo
) t ) t1 )

另外一种写法（这种写法存在问题）json_to_recordset

select * from json_to_recordset(
select array_to_json(array_agg(row_to_json(t1)))::jsonb as devsn from (
select
json_extract_path_text(devinfo, 'devsn')::json as devsn from (
select json_array_elements_text('[{"devtype": "1", "devtypename": "1", "devmanuid": "1", "devmanuname": "1", "devmode": "1", "devsn": "1", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}, {"devtype": "1", "devtypename": "1", "devmanuid": "1", "devmanuname": "1", "devmode": "1", "devsn": "1", "devmemo": "1","createdtime":"2019-01-21 10:22:15","modifiedtime":"2019-01-21 10:22:15"}]')::json as devinfo
) t ) t1 as
) as devsn(a varchar(64))