Extract all records from a JSON column, using JSON type

I have a couple tables (see reproducible code at the bottom):

tbl1_have

id  json_col                             
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}

tbl2_have

id  json_col                          
1   [{"a_i":"a","a_j":1},{"a_i":"b","a_j":2}]
2   [{"a_i":"c","a_j":3},{"a_i":"d","a_j":4}]

I wish to extract all json columns without providing explicit data type conversion for each columns since in my use case the names and amounts of nested attributes vary.

The expected output is the same for both cases:

tbl_want

id  a_i a_j                             
1   a   1
1   b   2
2   c   3
2   d   4

with a_i and a_j correctly stored as a character and numeric column, which mean I’d like to map json types to SQL types (say INT and VARCHAR() here) automatically.

The following gets me half way for both tables:

SELECT id, a_i, a_j FROM tbl2_have CROSS APPLY OPENJSON(json_col) 
WITH(a_i VARCHAR(100), a_j INT)

  id a_i a_j
1  1   a   1
2  1   b   2
3  2   c   3
4  2   d   4

How can I work around mentioning the types explicitly in with() ?

---

reproducible code :

CREATE TABLE tbl1_have (id INT, json_col VARCHAR(100))
INSERT INTO tbl1_have 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}')

CREATE TABLE tbl2_have (id INT, json_col VARCHAR(100))
INSERT INTO tbl2_have VALUES 
(1,   '[{"a_i":"a","a_j":1},{"a_i":"b","a_j":2}]'),
(2,   '[{"a_i":"c","a_j":3},{"a_i":"d","a_j":4}]')

SELECT id, a_i, a_j FROM tbl1_have CROSS APPLY OPENJSON(json_col) 
WITH(a_i VARCHAR(100), a_j INT)

SELECT id, a_i, a_j FROM tbl2_have CROSS APPLY OPENJSON(json_col) 
WITH(a_i VARCHAR(100), a_j INT)

Answer

I am assuming that you don’t know the name and type of keys in advance. You need to use dynamic SQL.

You first need to use OPENJSON without the WITH clause on the {objects} like so:

select string_agg(quotename(k) + case t
    when 0 then ' nchar(1)'       -- javascript null
    when 1 then ' nvarchar(max)'  -- javascript string
    when 2 then ' float'          -- javascript number
    when 3 then ' bit'            -- javascript boolean
    else ' nvarchar(max) as json' -- javascript array or object
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)

The inner query gives you the name and type of all the keys across all json values in the table. The outer query builds the WITH clause for dynamic SQL.

The rest is relatively straight forward, use the generated clause in your dynamic SQL. Here is the complete example:

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;

Demo on db<>fiddle