改进SQL Server查询以将任意表转换为JSON [英] Improve SQL Server query to convert arbitrary table to JSON

问题描述

经过大量搜索和拼凑而成的非常出色的技术，这些技术使用Web上的FOR XML和.nodes()命令来转换结果集，因此我能够创建此单个查询(而不是存储过程)，在将任意SQL查询转换为JSON数组方面做得相当好.

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.

该查询会将每个数据行编码为带有前导逗号的单个JSON对象. 数据行用方括号括起来，然后将整个结果集导出到文件中.

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 ']'

我对此的最大批评是，它运行缓慢.
目前大约需要3:30才能读取约42,000行.

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).

最后一个次要批评是，第一个数据行将在前面加逗号，从技术上讲，它不应该这样.为了弥补这一点，它需要在启动JSON数组的第一行中使用空的JSON对象.

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.

邀请了其他批评(最好是带有解决方案)，我唯一真正的限制是该解决方案在许多任意SQL查询中都可以很好地重复，而不必明确标识列名.

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.

我正在使用SQL Server 2012.

I'm using SQL Server 2012.

感谢和其他像我这样的人，他们正在寻找通用的SQL结果-> JSON数组转换器，请尽情享受！

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

推荐答案

我说，如果您真的想提高性能，请使用元编程.下面的示例使用40,000行进行尝试，并在不到一秒的时间内返回结果(不算插入初始的40k行，在此示例中仅需2秒钟左右).它还考虑到您的数据类型不要将数字用引号引起来.

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;

这篇关于改进SQL Server查询以将任意表转换为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！