语境
我有一个包含 JSON 数据的列的表。数组大小不是恒定的。大小可以大到 999。但是,如果需要,可以计算出确切的大小(在本例中为 12)。
CREATE TABLE TB_PRACTICE (
Id int Identity(1,1) NOT NULL PRIMARY KEY,
JsonCol nvarchar(max))
“JsonCol”列中的示例 JSON
[{"JsonId":85,"Values":[763,1356,1900,2419,2925,3420,3907,4389,4866,5338,5808,6274]}
,{"JsonId":86,"Values":[790,1391,1941,2465,2975,3474,3965,4450,4929,5405,5877,6346]}
,{"JsonId":87,"Values":[820,1429,1984,2514,3028,3531,4025,4514,4997,5475,5950,6422]}
,{"JsonId":88,"Values":[851,1469,2031,2566,3085,3592,4090,4582,5068,5550,6028,6502]}
,{"JsonId":89,"Values":[885,1512,2081,2622,3146,3657,4160,4655,5145,5630,6111,6589]}
,{"JsonId":90,"Values":[923,1560,2136,2683,3211,3728,4235,4734,5228,5716,6201,6682]}]
要求
我想将每个 JSON 对象转换为结果集,如下所示。数组中的项应该是结果集的列值。
jsonid | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
85 | 763 | 1356 | 1900 | 2419 | 2925 | 3420 | 3907 | 4389 | 4866 | 5338 | 5808 | 6274 |
... | ||||||||||||
... | ||||||||||||
90 | 923 | 1560 | 2136 | 2683 | 3211 | 3728 | 4235 | 4734 | 5228 | 5716 | 6201 | 6682 |
尝试
方法 1:在这里我必须遍历每个数组位置并将所需数据转储到临时表并动态形成结果集
Declare @i nvarchar(max)= '0';
Declare @comm nvarchar(max) =
'SELECT Id,JsonId,[Values]
FROM TB_PRACTICE
CROSS APPLY OPENJSON(JSON_QUERY(JsonCol, ''$''))
WITH (JsonId int ''$.JsonId'',
[Values] int ''$.Values['+@i+']'')
Where JsonCol not in ('''') '
exec(@comm)
方法 2:在这里,我将按行而不是按列获取数据
SELECT j1.JsonId, j2.[Values]
FROM OPENJSON((select top 1 JsonCol from TB_PRACTICE), '$') WITH (
JsonId int '$.JsonId',
[Values] nvarchar(max) '$.Values' AS JSON
) j1
CROSS APPLY OPENJSON(j1.[Values]) WITH (
[Values] int '$'
) j2
问题是否有一种简单的方法来实现要求,或者我是否必须使用上述方法之一来获得所需的结果集?
动态语句是一种选择。这个想法是获取所有不同的索引并WITH
动态构建子句:
DECLARE @stmt nvarchar(max)
-- Generate the schema
SELECT @stmt = (
SELECT CONCAT(N', [', (ArrayId + 1), N'] int ''$.Values[', ArrayId , N']''')
FROM (
SELECT DISTINCT CONVERT(int, j2.[key]) AS ArrayId
FROM TB_PRACTICE t
CROSS APPLY OPENJSON(t.JsonCol) j1
CROSS APPLY OPENJSON(j1.[value], '$.Values') j2
) t
ORDER BY ArrayId
FOR XML PATH('')
)
-- Generate the final statement
SELECT @stmt = CONCAT(
N'SELECT j.* ',
N'FROM TB_PRACTICE t ',
N'CROSS APPLY OPENJSON(t.JsonCol) WITH (',
N'JsonId int ''$.JsonId''',
@stmt,
N') j '
)
-- Execute the statement
DECLARE @err int
EXEC @err = sp_executesql @stmt
IF @err <> 0 PRINT 'Error'
结果:
jsonid | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
85 | 763 | 1356 | 1900 | 2419 | 2925 | 3420 | 3907 | 4389 | 4866 | 5338 | 5808 | 6274 |
86 | 790 | 1391 | 1941 | 2465 | 2975 | 3474 | 3965 | 4450 | 4929 | 5405 | 5877 | 6346 |
87 | 820 | 1429 | 1984 | 2514 | 3028 | 3531 | 4025 | 4514 | 4997 | 5475 | 5950 | 6422 |
88 | 851 | 1469 | 2031 | 2566 | 3085 | 3592 | 4090 | 4582 | 5068 | 5550 | 6028 | 6502 |
89 | 885 | 1512 | 2081 | 2622 | 3146 | 3657 | 4160 | 4655 | 5145 | 5630 | 6111 | 6589 |
90 | 923 | 1560 | 2136 | 2683 | 3211 | 3728 | 4235 | 4734 | 5228 | 5716 | 6201 | 6682 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句