将JSON中的数字转换为SQL表

发布于 2025-02-06 20:15:34 字数 4442 浏览 4 评论 0原文

上下文

我有一个具有包含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	5228	5716 6201	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

问题是否有一种简单的方法来达到要求，还是我必须使用上述方法之一来获得所需的结果集？

原文

CONTEXT

I have a table with a column that contains JSON data. The array size is not constant. The size can be as large as 999. However, the exact size can be figured out if needed (in this case it is 12).

CREATE TABLE TB_PRACTICE (
Id int Identity(1,1) NOT NULL PRIMARY KEY,
JsonCol nvarchar(max))

Sample JSON in the "JsonCol" column

 [{"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]}]

REQUIREMENT

I want to convert each JSON object to a result set as follows. Items in the array should be the column values of the result set.

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

ATTEMPTS

Approach 1: Here I would have to iterate through each array location and dump the required data to a temp table and dynamically form the result set

Declare @i nvarchar(max)= '0';
Declare @comm nvarchar(max) =
 'SELECT Id,JsonId,[Values]          
  FROM TB_PRACTICE             
  CROSS APPLY OPENJSON(JSON_QUERY(JsonCol, ''
Approach 2: Here, I will get data row wise and not column wise
   SELECT j1.JsonId, j2.[Values]
FROM OPENJSON((select top 1 JsonCol from TB_PRACTICE), '
Question

Is there a simple way to achieve the requirement or do I have to use one of the above approaches to get the desired result set?
'))             
  WITH (JsonId int ''$.JsonId'',           
  [Values] int ''$.Values['+@i+']'')           
  Where JsonCol not in ('''') '
 exec(@comm)

Approach 2: Here, I will get data row wise and not column wise

Question

Is there a simple way to achieve the requirement or do I have to use one of the above approaches to get the desired result set?
) WITH (
    JsonId int '$.JsonId',
    [Values] nvarchar(max) '$.Values'  AS JSON
) j1
CROSS APPLY OPENJSON(j1.[Values]) WITH (
    [Values] int '
Question

Is there a simple way to achieve the requirement or do I have to use one of the above approaches to get the desired result set?
'))             
  WITH (JsonId int ''$.JsonId'',           
  [Values] int ''$.Values['+@i+']'')           
  Where JsonCol not in ('''') '
 exec(@comm)

Approach 2: Here, I will get data row wise and not column wise

Question
Is there a simple way to achieve the requirement or do I have to use one of the above approaches to get the desired result set?

) j2

Question
Is there a simple way to achieve the requirement or do I have to use one of the above approaches to get the desired result set?

')) WITH (JsonId int ''$.JsonId'', [Values] int ''$.Values['+@i+']'') Where JsonCol not in ('''') ' exec(@comm)

Approach 2: Here, I will get data row wise and not column wise

Question
Is there a simple way to achieve the requirement or do I have to use one of the above approaches to get the desired result set?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谈情不如逗狗 2025-02-13 20:15:34

动态语句是一个选项。这个想法是要获取所有不同的索引，并动态地使用子句构建

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	4389	4866	5338 5338 5808	62746	6274 62748 6274 6274
86	790	1391	1941	2465	2975	3474	3965	4450	4929	5877	6346	87
820	1429	1984	2514	3028	3531	4025	4514	4514	4997	5475	502	5405
89	885	1512	2081	2622	3146	3657	5630	5145			4655	4160
											6111	6589
90	923	1560	2136	2683	3211	3728	4235	4734	5228	5716 6201	6201	6682

A dynamic statement is an option. The idea is to get all different indexes and build the WITH clause dynamically:

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'

Result:

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