我在 SQL Server 中決議 JSON 資料集，它適用于單個物件，但在呈現多個資料物件時會失敗。我認為這是因為 CROSS APPLY 陳述句。
在 JSON 資料集中，只有 4 條記錄，但我當前的 sql 回傳 16 條（4 個重復集，因為有 4 個交叉應用陳述句），但我不知道如何解決這個問題？
json
{
"type": "test",
"user": {
"last_update": "2022-06-19T14:13:07.707502 00:00",
"user_id": "12345"
},
"data": [
{
"metadata": {
"start_time": "2022-06-19T00:00:00 01:00",
"end_time": "2022-06-20T00:00:00 01:00"
},
"distance_data": {
"steps": 9299,
"distance_meters": 7704.0
}
},
{
"metadata": {
"start_time": "2022-06-17T00:00:00 01:00",
"end_time": "2022-06-18T00:00:00 01:00"
},
"distance_data": {
"steps": 2546,
"distance_meters": 2143.0
}
},
{
"metadata": {
"start_time": "2022-06-16T00:00:00 01:00",
"end_time": "2022-06-17T00:00:00 01:00"
},
"distance_data": {
"steps": 4969,
"distance_meters": 4192.0
}
},
{
"metadata": {
"start_time": "2022-06-18T00:00:00 01:00",
"end_time": "2022-06-19T00:00:00 01:00"
},
"distance_data": {
"steps": 6769,
"distance_meters": 5698.0
}
}
]
}
SQL 陳述句
SELECT
distance_meters, steps, cast(left(start_time,10) as date) startDate
FROM
OPENJSON ( @json )
WITH (
jType nvarchar(50) N'$.type',
jUser char(36) N'$.user.user_id',
data nvarchar(max) as JSON
) as a
CROSS APPLY
OPENJSON(a.data)
WITH
(
distance_data nvarchar(max) as json
) as b
CROSS APPLY
OPENJSON (b.distance_data)
WITH
(
distance_meters float,
steps int
) as c
CROSS APPLY
OPENJSON (a.data)
WITH
(
metadata nvarchar(max) as json
) as d
CROSS APPLY
OPENJSON (d.metadata)
WITH
(
start_time nvarchar(25),
end_time nvarchar(25)
) as e
ORDER BY startDate ASC;
uj5u.com熱心網友回復：
我認為您需要一個APPLY操作員：
SELECT j1.jType, j1.jUser, j2.*
FROM OPENJSON(@json) WITH (
jType nvarchar(50) N'$.type',
jUser char(36) N'$.user.user_id',
data nvarchar(max) as JSON
) AS j1
CROSS APPLY OPENJSON(j1.data) WITH (
start_time nvarchar(25) '$.metadata.start_time',
end_time nvarchar(25) '$.metadata.end_time',
steps numeric(10, 0) '$.distance_data.steps',
distance_meters numeric(10, 1) '$.distance_data.distance_meters'
) j2
結果：
| 型別 | j用戶 | 開始時間 | 時間結束 | 腳步 | distance_meters |
|---|---|---|---|---|---|
| 測驗 | 12345 | 2022-06-19T00:00:00 01:00 | 2022-06-20T00:00:00 01:00 | 9299 | 7704.0 |
| 測驗 | 12345 | 2022-06-17T00:00:00 01:00 | 2022-06-18T00:00:00 01:00 | 2546 | 2143.0 |
| 測驗 | 12345 | 2022-06-16T00:00:00 01:00 | 2022-06-17T00:00:00 01:00 | 4969 | 4192.0 |
| 測驗 | 12345 | 2022-06-18T00:00:00 01:00 | 2022-06-19T00:00:00 01:00 | 6769 | 5698.0 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/493660.html