Converting an array of numbers in a Json to a SQL table

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, ''$''))             
  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

   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

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?

Solution

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