pgsql字符串转数组，转json等

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

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 $$;