利用substr和instr 提取json中的内容

with  main as (
select  '{"output":{"setlinfo":{"cvlserv_pay":0,"insu_admdvs":"340202","hifdm_pay":0,"med_type":"21","psn_pay":1417.13,"hifmi_pay":0,"psn_no":"34020000000011908445","act_pay_dedc":900,"medins_setl_id":"H34020200019202406240720433916","acct_mulaid_pay":0,"oth_pay":0,"gend":"9","cashPayamt":1342.38,"mdtrt_id":"34022024062174018575","acct_pay":74.75,"dedc_std":0,"insutype":"310","inscp_scp_amt":2241.23,"crt_dedc":0,"psn_part_amt":1417.13,"psn_type":"11","overlmt_selfpay":175,"hifp_pay":1175.68,"preselfpay_amt":41.92,"clr_type":"21","othfund_pay":0,"cvlserv_flag":"0","mdtrt_cert_no":"B71428442","naty":"01","brdy":697046400000,"psn_cash_pay":1342.38,"certno":"340223199202033519","mdtrt_cert_type":"03","balc":0,"psn_cert_type":"01","hifob_pay":0,"medfee_sumamt":2592.81,"inscp_amt":2241.23,"hifes_pay":0,"fund_pay_sumamt":1175.68,"fulamt_ownpay_amt":134.66,"hosp_part_amt":0,"psn_name":"方海云","maf_pay":0,"psn_insu_rlts_id":"340200Y0000345307398","pool_prop_selfpay":0,"age":32},"setldetail":[{"fund_pay_type":"310101","fund_payamt":1175.68,"inscp_scp_amt":2241.23,"fund_pay_type_name":"城镇职工基本医疗保险统筹基金"},{"fund_pay_type":"310201","fund_payamt":74.75,"inscp_scp_amt":2241.23,"fund_pay_type_name":"城镇职工基本医疗保险个人账户基金"}]},"infcode":0,"inf_refmsgid":"340223202406240720470558031384"}'  as JYSC_MSG
from dual
)         
select  JYSC_MSG ,
--INSTR( JYSC_MSG,'acct_pay',1)  ,  SUBSTR( JYSC_MSG,  INSTR(JYSC_MSG,'acct_pay',1) + 10   ,  INSTR(JYSC_MSG,',',INSTR(JYSC_MSG,'acct_pay',1),1)-INSTR(JYSC_MSG,'acct_pay',1) - 10     )               
instr(  JYSC_MSG ,  '"psn_pay":'  ,1 )  ,  --键的初始位置
length('"psn_pay":')  , --键的长度
substr(JYSC_MSG   , instr(  JYSC_MSG ,  '"psn_pay":'  ,1 )   +10  ,   1  )  ,   --测试截取 值的第一个值
instr (  JYSC_MSG , ',' ,  instr(  JYSC_MSG ,  '"psn_pay":'  ,1 )   ,1     )   ,   --"," 值最后出现的 逗号的位置       

JSON_VALUE(   JSON_QUERY(   JSON_QUERY(JYSC_MSG, '$.output')  ,'$.setlinfo' )   , '$.psn_pay'   RETURNING NUMBER )                个人账户支付 ,


INSTR( JYSC_MSG,'psn_pay',1)  ,  SUBSTR( JYSC_MSG,  INSTR(JYSC_MSG,'psn_pay',1) + 10   ,  INSTR(JYSC_MSG,',',INSTR(JYSC_MSG,'psn_pay',1),1)-INSTR(JYSC_MSG,'psn_pay',1) - 10     )  as 李    
,
substr(   JYSC_MSG   ,  instr(  JYSC_MSG ,  '"psn_pay":'  ,1 )   +10  ,   instr (  JYSC_MSG , ',' ,  instr(  JYSC_MSG ,  '"psn_pay":'  ,1 ) +10   ,1 )   -   (instr(  JYSC_MSG ,  '"psn_pay":'  ,1 )  +10)   )
,
substr(   JYSC_MSG   ,  instr(  JYSC_MSG ,  '"insu_admdvs":"'  ,1 )   +15  ,   instr (  JYSC_MSG , '"' ,  instr(  JYSC_MSG ,  '"insu_admdvs":"'  ,1 )+15    ,1 )   -   (instr(  JYSC_MSG , '"insu_admdvs":"'  ,1 )  +15)   )
from      main