SQL Server 2019

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

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