SQL SERVER JSON_QUERY JSON_VALUE

response_json：

{
    "code": "000",
    "message": "成功",
    "data": {
        "secretKey": "",
        "content": "{\"rule_result\":{\"risk_level\":\"\",\"reason_code\":[],\"hitted_rules\":[{\"name\":\"ZZC_DLQ0021\",\"description\":\"申请人(身份证、手机）最近90天内命中早期逾期高风险或M3+逾期高风险\",\"rule_type\":\"跨机构比对\",\"risk_level\":\"H\"},{\"name\":\"ZZC_CRS0028\",\"description\":\"申请人最近7天到30天在消费金融机构出现过\",\"rule_type\":\"逾期比对\",\"risk_level\":\"M\"}]}}"
    }
}

{
    "code": "000",
    "message": "成功",
    "data": {
        "secretKey": "",
        "content": "{\"rule_result\":{\"risk_level\":\"\",\"reason_code\":[],\"hitted_rules\":[{\"name\":\"ZZC_DLQ0021\",\"description\":\"申请人(身份证、手机）最近90天内命中早期逾期高风险或M3+逾期高风险\",\"rule_type\":\"跨机构比对\",\"risk_level\":\"H\"},{\"name\":\"ZZC_CRS0028\",\"description\":\"申请人最近7天到30天在消费金融机构出现过\",\"rule_type\":\"逾期比对\",\"risk_level\":\"M\"}]}}"
    }
}

select
JSON_QUERY(JSON_VALUE(response_json,'$.data.content'),'$.rule_result.hitted_rules')[data.content.rule_result.hitted_rules]
,JSON_VALUE(JSON_VALUE(response_json,'$.data.content'),'$.rule_result.hitted_rules[0].name')[data.content.rule_result.hitted_rules0.name]
,JSON_VALUE(JSON_VALUE(response_json,'$.data.content'),'$.rule_result.hitted_rules[1].name')[data.content.rule_result.hitted_rules1.name]
,JSON_VALUE(JSON_VALUE(response_json,'$.data.content'),'$.rule_result.hitted_rules[2].name')[data.content.rule_result.hitted_rules2.name]

select
       JSON_QUERY(JSON_VALUE(response_json,'$.data.content'),'$.rule_result.hitted_rules') response_json
for json path

执行结果：

[
    {
        "response_json": [
            {
                "name": "ZZC_DLQ0021",
                "description": "申请人(身份证、手机）最近90天内命中早期逾期高风险或M3+逾期高风险",
                "rule_type": "跨机构比对",
                "risk_level": "H"
            },
            {
                "name": "ZZC_CRS0028",
                "description": "申请人最近7天到30天在消费金融机构出现过",
                "rule_type": "逾期比对",
                "risk_level": "M"
            }
        ]
    }
]