By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test(
id INT,
json_col NVARCHAR(MAX)
);
INSERT INTO test VALUES
(1, '{"a_i":"a","a_j":1}'),
(1, '{"a_i":"b","a_j":2}'),
(2, '{"a_i":"c","a_j":3}'),
(2, '{"a_i":"d","a_j":4}'),
(3, '[{"a_i":"a","a_j":1},{"a_i":"b","a_j":2}]'),
(4, '[{"a_i":"c","a_j":3},{"a_i":"d","a_j":4}]'),
(5, '[{"a_i":"!","x":null,"y":true,"z":false}]');
7 rows affected
select string_agg(quotename(k) + case t
when 0 then ' nchar(1)'
when 1 then ' nvarchar(max)'
when 2 then ' float'
when 3 then ' bit'
else ' nvarchar(max) as json'
end, ', ') within group (order by k)
from (
select j2.[key], max(j2.[type])
from test
cross apply openjson(case when json_col like '{%}' then '[' + json_col + ']' else json_col end) as j1
cross apply openjson(j1.value) as j2
group by j2.[key]
) as kt(k, t)
| (No column name) |
|---|
| [a_i] nvarchar(max), [a_j] float, [x] nchar(1), [y] bit, [z] bit |
declare @table_name nvarchar(100) = 'test';
declare @with_clause nvarchar(100);
declare @query1 nvarchar(999) = N'select @with_clause_temp = string_agg(quotename(k) + case t
when 0 then '' nchar(1)''
when 1 then '' nvarchar(max)''
when 2 then '' float''
when 3 then '' bit''
else '' nvarchar(max) as json''
end, '', '') within group (order by k)
from (
select j2.[key], max(j2.[type])
from ' + quotename(@table_name) + '
cross apply openjson(case when json_col like ''{%}'' then ''['' + json_col + '']'' else json_col end) as j1
cross apply openjson(j1.value) as j2
group by j2.[key]
) as kt(k, t)';
exec sp_executesql @query1, N'@with_clause_temp nvarchar(100) out', @with_clause out;
declare @query2 nvarchar(999) = N'select id, j.*
from ' + quotename(@table_name) + '
cross apply openjson(json_col)
with (' + @with_clause + ') as j';
exec sp_executesql @query2;
| id | a_i | a_j | x | y | z |
|---|---|---|---|---|---|
| 1 | a | 1 | null | null | null |
| 1 | b | 2 | null | null | null |
| 2 | c | 3 | null | null | null |
| 2 | d | 4 | null | null | null |
| 3 | a | 1 | null | null | null |
| 3 | b | 2 | null | null | null |
| 4 | c | 3 | null | null | null |
| 4 | d | 4 | null | null | null |
| 5 | ! | null | null | True | False |