I am getting 0 row when selecting from OPENJSON below. I am expecting to get all 3 values "AA" for different as_of_date for CREDIT_RTG . I tried different select statements. I am running in SQL 2016. Expected result should be similar to this
Codes are posted below. Thanks in advance
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 ISSUERID, CREDIT_RTG, as_of_date
FROM OPENJSON(@jsonVal,'$.result.data.factors')
with
(
[ISSUERID] [varchar](20),
[ISSUER_NAME] [varchar](50) ,
CREDIT_RTG [varchar](20),
as_of_date [varchar](20),
value [varchar](20),
)
2条答案
按热度按时间cl25kdpy1#
The way you are parsing the JSON appears to be unusual. It seems the
data_values
arrays are correlated across differentfactors
objects.So you need to first parse out the
factors
array into separate JSON sub-arrays, and pivot it up into columns. Then you can split out theISSUERID
array first, and correlate back to the others usingJSON_VALUE
. To get the array index for each row, you first need a separate call toOPENJSON
with no schema, which returnskey, value
where thekey
is the index into the array.Note that because both
$.result.data
andfactors
are arrays, you need separateOPENJSON
calls to split them out. If there is only ever onedata
object then you can combine them with justOPENJSON(@jsonval, '$.result.data[0].factors')
.db<>fiddle
SQL Server 2016 does not allow dynamic JSON paths, so instead you need to do a
JOIN
between all the different arrays based onkey
db<>fiddle
owfi6suc2#
Here's a bit simpler, albeit likely less performant openjson version:
It's a group by that fetches all the factors arrays and then pivots the fields depending on which array it operates on by using the array index as grouping field.
It's easy to add other fields by changing the json_values.