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

Question

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?

Why are you using dynamic SQL here? Could the number of values (in the array) change? — Thom A
– Thom A ♦, Commented Jun 13, 2022 at 10:13
This sounds more like this is something that should be done in the presentation layer, not the SQL layer. — Thom A
– Thom A ♦, Commented Jun 13, 2022 at 10:22
@Sampath, do you know the maximum possible count of items in the $.Values JSON array? — Zhorov
– Zhorov, Commented Jun 13, 2022 at 10:39
@Zhorov The count can be as large as 999. However, the exact size can be figured out if needed. — Sampath
– Sampath, Commented Jun 13, 2022 at 10:48

Zhorov · Accepted Answer · 2022-06-13 11:33:52Z

1

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

edited Jun 13, 2022 at 11:33

answered Jun 13, 2022 at 11:28

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sampath Over a year ago

I used the above solution plus by joining a temp table having 2 column (HeaderName & ROW_NUMBER()), I was able to assign column name as well. JOIN #temp tmp ON tmp.Row_Number = (ArrayId + 1). And change SELECT CONCAT(N', [', (ArrayId + 1), N'] int ''$.Values[', ArrayId , N']''') to SELECT CONCAT(N', [', tmp.HeaderName , N'] int ''$.Values[', ArrayId , N']''')

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related