英文:
SELECT * FROM OPENJSON -- Not getting expected values
问题
Here is the translated code section:
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":
<details>
<summary>英文:</summary>
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
[enter image description here](https://i.stack.imgur.com/oM0Ij.png)
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,
[ISSUER_NAME] varchar ,
CREDIT_RTG varchar,
as_of_date varchar,
value varchar,
)
</details>
# 答案1
**得分**: 2
以下是您要翻译的内容：
"The way you are parsing the JSON appears to be unusual. It seems the `data_values` arrays are correlated across different `factors` 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 the `ISSUERID` array first, and correlate back to the others using `JSON_VALUE`. To get the array index for each row, you first need a separate call to `OPENJSON` with no schema, which returns `key, value` where the `key` is the index into the array.
Note that because both `$.result.data` and `factors` are arrays, you need separate `OPENJSON` calls to split them out. If there is only ever one `data` object then you can combine them with just `OPENJSON(@jsonval, '$.result.data[0].factors')`.
SQL Server 2016 does not allow dynamic JSON paths, so instead you need to do a `JOIN` between all the different arrays based on `key`."
请注意，上述内容包括代码示例，我已将其排版为中文文本。
<details>
<summary>英文:</summary>
The way you are parsing the JSON appears to be unusual. It seems the `data_values` arrays are correlated across different `factors` 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 the `ISSUERID` array first, and correlate back to the others using `JSON_VALUE`. To get the array index for each row, you first need a separate call to `OPENJSON` with no schema, which returns `key, value` where the `key` is the index into the array.
Note that because both `$.result.data` and `factors` are arrays, you need separate `OPENJSON` calls to split them out. If there is only ever one `data` object then you can combine them with just `OPENJSON(@jsonval, '$.result.data[0].factors')`.
```tsql
SELECT
final.*
FROM OPENJSON(@jsonVal,'$.result.data')
WITH
(
factors nvarchar(max) AS JSON
) j1
CROSS APPLY (
SELECT pvt.*
FROM OPENJSON(j1.factors)
WITH
(
name varchar(50),
data_values nvarchar(max) AS JSON
) j2
PIVOT (MAX(data_values) FOR name IN
(
ISSUERID, ISSUER_NAME, CREDIT_RTG
)
) pvt
) pvt
CROSS APPLY (
SELECT
[ISSUERID] = issuerid.value,
[ISSUER_NAME] = JSON_VALUE(pvt.ISSUER_NAME, '$[' + array.[key] + '].value'),
CREDIT_RTG = JSON_VALUE(pvt.CREDIT_RTG, '$[' + array.[key] + '].value'),
issuerid.as_of_date
FROM OPENJSON(pvt.ISSUERID) array
CROSS APPLY OPENJSON(array.value)
WITH
(
value varchar(50),
as_of_date date
) issuerid
) final;
SQL Server 2016 does not allow dynamic JSON paths, so instead you need to do a JOIN between all the different arrays based on key
-- etc
CROSS APPLY (
SELECT
[ISSUERID] = issuerid.value,
[ISSUER_NAME] = JSON_VALUE(arrISSUER_NAME.value, '$.value'),
CREDIT_RTG = JSON_VALUE(arrCREDIT_RTG.value, '$.value'),
issuerid.as_of_date
FROM OPENJSON(pvt.ISSUERID) arrISSUERID
JOIN OPENJSON(pvt.ISSUER_NAME) arrISSUER_NAME ON arrISSUER_NAME.[key] = arrISSUERID.[key]
JOIN OPENJSON(pvt.CREDIT_RTG ) arrCREDIT_RTG ON arrCREDIT_RTG.[key] = arrISSUERID.[key]
CROSS APPLY OPENJSON(arrISSUERID.value)
WITH
(
value varchar(50),
as_of_date date
) issuerid
) final
答案2
得分: 1
以下是代码的翻译部分：
select max(case when json_value(x.value, '$.name') = 'ISSUER_ISIN' then json_value(y.value, '$.value') end) AS ISSUER_ISIN
, max(case when json_value(x.value, '$.name') = 'ISSUER_SEDOL' then json_value(y.value, '$.value') end) AS ISSUER_SEDOL
, max(case when json_value(x.value, '$.name') = 'ISSUERID' then json_value(y.value, '$.value') end) AS ISSUERID
, max(case when json_value(x.value, '$.name') = 'CREDIT_RTG' then json_value(y.value, '$.value') end) AS CREDIT_RTG
, max(case when json_value(x.value, '$.name') = 'CREDIT_RTG' then json_value(y.value, '$.as_of_date') end) AS as_of_date
from openjson(@jsonVal, '$.result.data[0].factors') x
cross apply openjson(x.value,'$.data_values') y
group by y.[key]
这是一个按照不同数组的字段进行数据透视的分组查询，根据数组索引作为分组字段。通过更改json_values，可以轻松添加其他字段。
英文:
Here's a bit simpler, albeit likely less performant openjson version:
select max(case when json_value(x.value, '$.name') = 'ISSUER_ISIN' then json_value(y.value, '$.value') end) AS ISSUER_ISIN
, max(case when json_value(x.value, '$.name') = 'ISSUER_SEDOL' then json_value(y.value, '$.value') end) AS ISSUER_SEDOL
, max(case when json_value(x.value, '$.name') = 'ISSUERID' then json_value(y.value, '$.value') end) AS ISSUERID
, max(case when json_value(x.value, '$.name') = 'CREDIT_RTG' then json_value(y.value, '$.value') end) AS CREDIT_RTG
, max(case when json_value(x.value, '$.name') = 'CREDIT_RTG' then json_value(y.value, '$.as_of_date') end) AS as_of_date
from openjson(@jsonVal, '$.result.data[0].factors') x
cross apply openjson(x.value,'$.data_values') y
group by y.[key]
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论