SQL Server 2016中OPENJSON查询返回0行，无法获取CREDIT_RTG的3条AA值求助

嘿，我瞅了下你遇到的问题，你当前的OPENJSON查询路径和结构映射都有问题，所以才会返回0行。咱们来一步步修正它：

问题根源 #

1. 路径错误：你的JSON里$.result.data是一个数组（用[]包裹），直接写$.result.data.factors是无效的，必须先遍历data数组里的元素，才能访问其中的factors。
2. 结构映射错误：ISSUERID、CREDIT_RTG这些字段并不是factors数组元素的直接属性——CREDIT_RTG是factors中name为"CREDIT_RTG"的元素下，data_values数组里的value值；ISSUERID则来自issuer_metadata数组。需要多层嵌套解析才能拿到这些数据。

修正后的查询代码 #

DECLARE @jsonVal varchar(max)
SET @jsonVal = '{
"status": "OK",
"code": 200,
"trace_id": "3eea64f2a7917c11",
"timestamp": "2023-05-31T14:36:02Z",
"messages": [],
"result": {
"response_metadata": {
"total_number_of_instruments": 1,
"data_request_id_expiration_time": "2023-06-01T14:36:02+0000",
"resolvedfactors": [
"CREDIT_RTG",
"ISSUER_NAME",
"ISSUER_ISIN",
"ISSUER_SEDOL",
"ISSUERID"
]
},
"data": [
{
"requested_id": "IID000000002745031",
"issuer_metadata": [
{
"ISSUERID": "IID000000002745031",
"ISSUER_NAME": "ALPHABET INC.",
"ISSUER_ISIN": "US02079K3059",
"ISSUER_TICKER": "GOOGL",
"as_of_date": "2019-09-01",
"valid_until_date": "2019-09-14"
},
{
"ISSUERID": "IID000000002745031",
"ISSUER_NAME": "ALPHABET INC.",
"ISSUER_ISIN": "US02079K1079",
"ISSUER_TICKER": "GOOGL",
"as_of_date": "2019-09-14",
"valid_until_date": "2019-12-01"
}
],
"factors": [
{
"name": "CREDIT_RTG",
"data_values": [
{
"value": "AA",
"as_of_date": "2019-09-30",
"as_at_date": "2023-05-31T14:36:02.245957Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "AA",
"as_of_date": "2019-10-31",
"as_at_date": "2023-05-31T14:36:02.245957Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "AA",
"as_of_date": "2019-11-29",
"as_at_date": "2023-05-31T14:36:02.245957Z",
"reference_issuer_id": null,
"reference_issuer_name": null
}
]
},
{
"name": "ISSUER_NAME",
"data_values": [
{
"value": "ALPHABET INC.",
"as_of_date": "2019-09-30",
"as_at_date": "2023-05-31T14:36:02.246042Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "ALPHABET INC.",
"as_of_date": "2019-10-31",
"as_at_date": "2023-05-31T14:36:02.246042Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "ALPHABET INC.",
"as_of_date": "2019-11-29",
"as_at_date": "2023-05-31T14:36:02.246042Z",
"reference_issuer_id": null,
"reference_issuer_name": null
}
]
},
{
"name": "ISSUER_ISIN",
"data_values": [
{
"value": "US02079K1079",
"as_of_date": "2019-09-30",
"as_at_date": "2023-05-31T14:36:02.246006Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "US02079K1079",
"as_of_date": "2019-10-31",
"as_at_date": "2023-05-31T14:36:02.246006Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "US02079K1079",
"as_of_date": "2019-11-29",
"as_at_date": "2023-05-31T14:36:02.246006Z",
"reference_issuer_id": null,
"reference_issuer_name": null
}
]
},
{
"name": "ISSUER_SEDOL",
"data_values": [
{
"value": "BYY88Y7",
"as_of_date": "2019-09-30",
"as_at_date": "2023-05-31T14:36:02.246084Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "BYY88Y7",
"as_of_date": "2019-10-31",
"as_at_date": "2023-05-31T14:36:02.246084Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "BYY88Y7",
"as_of_date": "2019-11-29",
"as_at_date": "2023-05-31T14:36:02.246084Z",
"reference_issuer_id": null,
"reference_issuer_name": null
}
]
},
{
"name": "ISSUERID",
"data_values": [
{
"value": "IID000000002745031",
"as_of_date": "2019-09-30",
"as_at_date": "2023-05-31T14:36:00Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "IID000000002745031",
"as_of_date": "2019-10-31",
"as_at_date": "2023-05-31T14:36:00Z",
"reference_issuer_id": null,
"reference_issuer_name": null
},
{
"value": "IID000000002745031",
"as_of_date": "2019-11-29",
"as_at_date": "2023-05-31T14:36:00Z",
"reference_issuer_id": null,
"reference_issuer_name": null
}
]
}
]
}
]
}
}'

-- 正确的查询语句
SELECT 
    im.ISSUERID,
    dv.value AS CREDIT_RTG,
    dv.as_of_date
FROM OPENJSON(@jsonVal, '$.result.data') AS data -- 遍历最外层的data数组
CROSS APPLY OPENJSON(data.value, '$.issuer_metadata') -- 解析issuer_metadata获取ISSUERID
WITH (
    ISSUERID varchar(20) '$.ISSUERID'
) AS im
CROSS APPLY OPENJSON(data.value, '$.factors') AS factors -- 遍历factors数组
CROSS APPLY OPENJSON(factors.value, '$.data_values') -- 解析目标factor的data_values数组
WITH (
    value varchar(20) '$.value',
    as_of_date varchar(20) '$.as_of_date'
) AS dv
WHERE factors.name = 'CREDIT_RTG'; -- 筛选出CREDIT_RTG对应的记录

查询逻辑说明 #

1. 第一步用OPENJSON(@jsonVal, '$.result.data')遍历data数组，拿到里面的单个仪器数据元素。
2. 通过CROSS APPLY关联解析issuer_metadata数组，提取出ISSUERID（这里所有元数据的ISSUERID一致，所以结果不会重复）。
3. 再用CROSS APPLY遍历factors数组，通过WHERE factors.name = 'CREDIT_RTG'筛选出信用评级对应的factor。
4. 最后解析该factor下的data_values数组，取出每个评级的value（即AA）和对应的as_of_date。

执行这个查询后，就能得到你期望的3条记录啦！

备注：内容来源于stack exchange，提问作者Overflow