SQL Server语言 - 解析json与多个对象

Question

我正在解析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;

Answer 1

我认为你需要一个单一的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

结果:

<表类>jType汁液start_timeend_time步骤distance_meterstbody><<tr>测试123452022 - 06 - 19 - t00:00:00 + 01:002022 - 06 - 20 - t00:00:00 + 01:0092997704.0测试123452022 - 06 - 17 - t00:00:00 + 01:002022 - 06 - 18 - t00:00:00 + 01:0025462143.0测试123452022 - 06 - 16 - t00:00:00 + 01:002022 - 06 - 17 - t00:00:00 + 01:0049694192.0测试123452022 - 06 - 18 - t00:00:00 + 01:002022 - 06 - 19 - t00:00:00 + 01:0067695698.0