pgsql字符串转数组,转json等
do $$ declare str text :='{ "tzmxList": [ { "ZCPH": "CPH000000001", "CPID": "1111", "SYJB": "11", "TZJE": "1000000" }, { "ZCPH": "CPH000000002", "CPID": "2222", "SYJB": "22", "TZJE": "2000000" }, { "ZCPH": "CPH000000003", "CPID": "3333", "SYJB": "33", "TZJE": "3000000" } ] }'; note varchar; paramlist jsonb; tzmxList jsonb; code int; rec jsonb; V_ZCPH varchar(20); -- 子产品账号 V_CPID integer; -- 投资产品 V_SYJB integer; -- 受益级别 V_TZJE decimal(20,2); -- 投资金额 begin paramlist := str; if paramlist ? 'tzmxList' then tzmxList := paramlist->'tzmxList'; code := jsonb_array_length(tzmxList); raise notice 'exists:count=%,%',code,tzmxList; for rec in select value from jsonb_array_elements(tzmxList) loop V_ZCPH := rec->>'ZCPH'; --获取 子产品账号 V_CPID := rec->>'CPID'; --获取 投资产品 V_SYJB := rec->>'SYJB'; --获取 受益级别 V_TZJE := rec->>'TZJE'; --获取 投资金额 raise notice 'exists:count=%,%,%,%',V_ZCPH,V_CPID,V_SYJB,V_TZJE; end loop; else raise notice 'not exists'; end if; end $$;