将 Json 中的数字数组转换为 SQL 表

桑帕斯

语境

我有一个包含 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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章