oracle 查询 返回 jsOn,oracle 正則查詢json返回報文中某個字段的值

接口返回報文為json 格式，如下：

{"body":{"businessinfo":{"c1rate":"25.00","c2rate":"0.00","c4rate":"0.00","c5rate":"0.00","c6rate":"6.00","c8rate":"0.00","d1rate":"0.00","d2rate":"1.00","riskcode":"0355","ruleid":"R74000154606_11-0"},"vehicleinfo":{"c1rate":"4.00","c2rate":"0.00","c4rate":"0.00","c5rate":"0.00","c6rate":"6.00","c8rate":"0.00","d1rate":"0.00","d2rate":"1.00","riskcode":"0330","ruleid":"R74000154617_11-0"}},"head":{"error_code":"0000","error_message":"成功","request_type":"???","response_code":"0"}}

獲取返回報文中，c1rate的值，用到oracle 正則查詢

SQL

select i.c_app_no, i.c_req_content as reuqest,

i.c_res_content as response,

REGEXP_SUBSTR(c_res_content, '\"c1rate\":\"([0-9.]+)\"', 1, 1, 'i') as c,

REGEXP_SUBSTR(REGEXP_SUBSTR(c_res_content, '\"c1rate\":\"([0-9.]+)\"', 1, 1, 'i'),'([0-9.]{2,})',1,1,'i') as d

from WEB_INTERFACE_SOAP_CONTENT i

WHERE c_app_no = '5997499035520170000382' and

c_interface_code = 'FeeContrlRequest'