postgresql 存储过程处理json字符串

函数的参数传入值为json格式的字符串，通过遍历，获取某个字段值。之后进行处理。

下面的示例中，

p_Data进行了赋值，数组长度是2.

{
    "data": [
        {
            "_id": "fbc0sdfsdfdgdf",
            "Name": "手环4492",
            "PWD": "123456",
            "SIM": "",
            "MID": "68946470303dsg4492",
            "FAC": "XT",
            "Model": "T6 Pro",
            "ImgID": "e476005866sdfa2a6407502b3",
            "RT": "2020-04-18 16:57:31",
            "Time": "2020-07-17 15:10:59",
            "Tag": "happy",
            "B": 100,
            "WID": "d7947802",
            "PEDO": 0,
            "Step": 0,
            "Profile": 2,
            "SL": "",
            "Admin": "sdfgreytr",
            "Guarder": "1890120526309",
            "Itvl": 1,
            "LowBat": 1,
            "AR": 1,
            "Wear": 0,
            "PID": "JX",
            "DCT": 5,
            "FL": 4403675,
            "Auth": 1,
            "T": 0,
            "Keybrd": 1,
            "UT": "2020-07-27 14:22:11",
            "FUT": "2020-06-22 14:54:12",
            "Host": "10-9-116-82",
            "PMT": "2020072711",
            "SocTS": 1595830678,
            "GuarderFail": null,
            "SOS": "18901205263",
            "SOSFail": null,
            "PSL": "18901205263",
            "PNL": "神兽",
            "PHLFail": null,
            "SR": 1,
            "SNum": 7,
            "SDay": "20200727",
            "PUT": "2020-07-27 14:22:05",
            "GSM": 100,
            "W": 36,
            "WTime": "13:47",
            "WT": {
                "T": 1595828764797,
                "Lon": 116.368944,
                "Lat": 40.060245,
                "B": "WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"
            },
            "HWf": 1,
            "FQCY": 60,
            "HGps": 1,
            "ETWD": 230720,
            "UID": "fbc09a5ea974f60d4713ff9f",
            "Pro": "北京市",
            "City": "北京市",
            "Dist": "海淀区",
            "Str": "建材城东路",
            "Lon": 116.3690191,
            "Lat": 40.0602989,
            "Radius": 50,
            "Desc": ""
        }
    ],
    "Ret": "Succ"
}

CREATE OR REPLACE FUNCTION "esys"."fntest123"()
  RETURNS "pg_catalog"."void" AS $BODY$ BEGIN-- Routine body goes here...
	DECLARE
		p_Data VARCHAR (4000);
		ppp varchar(1000);
		lll varchar(1000);
		tempdata varchar;
		rs          record;
	BEGIN
			p_Data := '[{"_id":"fbc09a5ea974f60d4713ff9f","Name":"手环4492","PWD":"123456","SIM":"","MID":"689464703034492","FAC":"XT","Model":"T6 Pro","ImgID":"e476005866e8a2a6407502b3","RT":"2020-04-18 16:57:31","Time":"2020-07-17 15:10:59","Tag":"happy","B":100,"WID":"d7947802","PEDO":0,"Step":0,"Profile":2,"SL":"","Admin":"4d17115f6d3a18b7152aec53","Guarder":"18901205263","Itvl":1,"LowBat":1,"AR":1,"Wear":0,"PID":"JX","DCT":5,"FL":4403675,"Auth":1,"T":0,"Keybrd":1,"UT":"2020-07-27 14:48:10","FUT":"2020-06-22 14:54:12","Host":"10-9-116-251","PMT":"2020072711","SocTS":1595832003,"SOS":"18901205263","PSL":"18901205263","PNL":"神兽","SR":1,"SNum":23,"SDay":"20200727","PUT":"2020-07-27 14:48:05","GSM":100,"W":36,"WTime":"13:47","WT":{"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"},"HWf":1,"FQCY":60,"HGps":1,"ETWD":230720,"UID":"fbc09a5ea974f60d4713ff9f","Pro":"北京市","City":"北京市","Dist":"海淀区","Str":"西三旗街道建材城东路二十一世纪大厦(建材城东路)","Lon":116.3685121,"Lat":40.061043,"Radius":75,"Desc":""},{"_id":"fbc09a5ea974f60d4713ff9f","Name":"手环4492","PWD":"123456","SIM":"","MID":"689464703034492","FAC":"XT","Model":"T6 Pro","ImgID":"e476005866e8a2a6407502b3","RT":"2020-04-18 16:57:31","Time":"2020-07-17 15:10:59","Tag":"happy","B":100,"WID":"d7947802","PEDO":0,"Step":0,"Profile":2,"SL":"","Admin":"4d17115f6d3a18b7152aec53","Guarder":"18901205263","Itvl":1,"LowBat":1,"AR":1,"Wear":0,"PID":"JX","DCT":5,"FL":4403675,"Auth":1,"T":0,"Keybrd":1,"UT":"2020-07-27 14:48:10","FUT":"2020-06-22 14:54:12","Host":"10-9-116-251","PMT":"2020072711","SocTS":1595832003,"SOS":"18901205263","PSL":"18901205263","PNL":"神兽","SR":1,"SNum":23,"SDay":"20200727","PUT":"2020-07-27 14:48:05","GSM":100,"W":36,"WTime":"13:47","WT":{"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"},"HWf":1,"FQCY":60,"HGps":1,"ETWD":230720,"UID":"fbc09a5ea974f60d4713ff9f","Pro":"北京市","City":"北京市","Dist":"海淀区","Str":"西三旗街道建材城东路二十一世纪大厦(建材城东路)","Lon":116.3685121,"Lat":40.061043,"Radius":75,"Desc":""}]';
		
		
    for rs in (select "value" as single_value from json_array_elements_text(p_Data::JSON)) loop
			select t1.pwd::text into ppp from (select (rs.single_value) ::JSON #>> '{PWD}' as pwd) as t1;
			raise notice 'pwd is %',ppp;
			select t1.pwd::text into ppp from (select (rs.single_value) ::JSON #>> '{WT}' as pwd) as t1;
			raise notice 'wt is %',ppp;
			select t1.pwd::text into lll from (select (rs.single_value) ::JSON #>> '{Lon}' as pwd) as t1;
		  raise notice 'wt_lon %',lll;
		end loop;
         
						

	END;
	RETURN;

END $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

输出结果如下

注意:  pwd is 123456
注意:  wt is {"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"}
注意:  wt_lon 116.3685121
注意:  pwd is 123456
注意:  wt is {"T":1595828764797,"Lon":116.368944,"Lat":40.060245,"B":"WT,20-07-27,13:46:04,d2f5,1,24,-1,40,53174EAC5E02"}
注意:  wt_lon 116.3685121

Procedure executed successfully

时间: 0.021s