Improve SQL Server query to convert arbitrary table to JSON

Question

After a lot of searching and piecing together the very excellent techniques for converting result sets using the FOR XML and .nodes() commands that are around the web, I was able to create this single query (not a stored procedure) which does a reasonably good job of converting any arbitrary SQL query to a JSON array.

The query will encode each data row as a single JSON object with a leading comma. The data rows are wrapped by brackets and the whole result set is then expected to be exported to a file.

I'd like to see if anyone out there can see ways to improve its performance?

Here's the query with a sample table:

declare @xd table (col1 varchar(max), col2 int, col3 real, colNull int) 

insert into @xd 
select '', null, null, null
UNION ALL select 'ItemA', 123, 123.123, null
UNION ALL select 'ItemB', 456, 456.456, null
UNION ALL select '7890', 789, 789.789, null

select '[{}'
UNION ALL
select ',{' + STUFF((
    (select ','
        + '"' + r.value('local-name(.)', 'varchar(max)') + '":'
        + case when r.value('./@xsi:nil', 'varchar(max)') = 'true' then 'null'
        when isnumeric(r.value('.', 'varchar(max)')) = 1
            then r.value('.', 'varchar(max)')
        else '"' + r.value('.', 'varchar(max)') + '"'
        end
    from rows.nodes('/row/*') as x(r) for xml path(''))
    ), 1, 1, '') + '}'
from (
    -- Arbitrary query goes here, (fields go where t.* is, table where @xd t is)
    select (select t.* for xml raw,type,elements XSINIL) rows
    from @xd t
) xd
UNION ALL
select ']'

My biggest critique of it, is that it's insanely slow.
It currently takes about 3:30 for ~42,000 rows.

My other big critique is that it currently assumes that everything that looks like a number is a number. It doesn't try to discover column type in the least (and I'm not even sure if it can).

A final minor critique is that the first data row will have a comma up front and technically it shouldn't. To compensate for that it requires that empty JSON object in the first row that starts the JSON array.

Other critiques (preferably with solutions) invited, the only real limitation I have is that the solution be decently repeatable on many arbitrary SQL queries without having to explicitly identify the column names.

I'm using SQL Server 2012.

Thanks and to anyone else like me who was looking for a generalized SQL Results -> JSON Array converter, ENJOY!

Answer 1

I say if you really want to kick up performance, use metaprogramming. The example below tries this with 40,000 rows and returns results in less than a second (not counting inserting the initial 40k rows, which in this example only takes about 2 seconds). It also takes into account your data types to not enclose numbers in quotes.

declare @xd table (col1 varchar(max), col2 int, col3 real, colDate datetime, colNull int);

declare @i int = 0;

while @i < 10000 begin
    set @i += 1;
    insert into @xd
    select '', null, null, null, null
    union all select 'ItemA', 123, 123.123, getDate(), null
    union all select 'ItemB', 456, 456.456, getDate(), null
    union all select '7890', 789, 789.789, getDate(), null;
end;

select *
into #json_base
from (
    -- Insert SQL Statement here
    select * from @xd
) t;

declare @columns table (
    id int identity primary key,
    name sysname,
    datatype sysname,
    is_number bit,
    is_date bit);

insert into @columns(name, datatype, is_number, is_date)
select columns.name, types.name,
       case when number_types.name is not NULL
            then 1 else 0
       end as is_number,
       case when date_types.name is not NULL
            then 1 else 0
       end as is_date
from tempdb.sys.columns
join tempdb.sys.types
    on (columns.system_type_id = types.system_type_id)
left join (values ('int'), ('real'), ('numeric'),
                  ('decimal'), ('bigint'), ('tinyint')) as number_types(name)
    on (types.name = number_types.name)
left join (values ('date'), ('datetime'), ('datetime2'),
                  ('smalldatetime'), ('time'), ('datetimeoffset')) as date_types(name)
    on (types.name = date_types.name)
where object_id = OBJECT_ID('tempdb..#json_base');

declare @field_list varchar(max) = STUFF((
    select '+'',''+' + QUOTENAME(QUOTENAME(name, '"') + ':', '''')
           + '+' + case when is_number = 1
                        then 'COALESCE(LTRIM('
                                + QUOTENAME(name) + '),''null'')'
                        when is_date = 1
                        then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max), '
                                + QUOTENAME(name) + ', 126)),''"''),''null'')'
                        else 'COALESCE(QUOTENAME('
                                + QUOTENAME(name) + ',''"''),''null'')'
                   end
    from @columns
    for xml path('')),
    1, 5, '');

create table #json_result (
    id int identity primary key,
    line varchar(max));

declare @sql varchar(max) = REPLACE(
    'insert into #json_result '
  + 'select '',{''+{f}+''}'' '
  + 'from #json_base', '{f}', @field_list);

exec(@sql);

update #json_result
set line = STUFF(line, 1, 1, '')
where id = 1;

select '['
UNION ALL
select line
from #json_result
UNION ALL
select ']';

drop table #json_base;
drop table #json_result;

Answer 2

From Firoz Ansari:

CREATE PROCEDURE [dbo].[GetJSON] (
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString = CAST(@XML AS VARCHAR(MAX))

DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @Key VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)

DECLARE @StartRoot VARCHAR(100); SET @StartRoot = ''
DECLARE @EndRoot VARCHAR(100); SET @EndRoot = ''
DECLARE @StartField VARCHAR(100); SET @StartField = ''

SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
SET @JSON = ''
WHILE @RowStart > 0
BEGIN
    SET @RowStart = @RowStart+Len(@StartRoot)
    SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
    SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)
    SET @JSON = @JSON+'{'

    -- for each row
    SET @FieldStart = CharIndex(@StartField, @Row, 0)
    WHILE @FieldStart > 0
    BEGIN
        -- parse node key
        SET @FieldStart = @FieldStart+Len(@StartField)
        SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
        SET @Key = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
        SET @JSON = @JSON+'"'+@Key+'":'

        -- parse node value
        SET @FieldStart = @FieldEnd+1
        SET @FieldEnd = CharIndex('0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
    SET @JSON = @JSON+'},'
    --/ for each row

    SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
END
IF LEN(@JSON) > 0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
SET @JSON = '[' + @JSON + ']'
SELECT @JSON
END