postgresql 函数运用

本贴最后更新于 1890 天前,其中的信息可能已经沧海桑田

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 ) 

上面这个语句地方
imagepng
这个地方一定不能在进行 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))

  • SQL
    124 引用 • 296 回帖 • 3 关注
  • JSON

    JSON (JavaScript Object Notation)是一种轻量级的数据交换格式。易于人类阅读和编写。同时也易于机器解析和生成。

    51 引用 • 190 回帖 • 1 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...