经过大量搜索和拼凑而成的非常出色的技术,这些技术使用Web上的FOR
XML和.nodes()命令来转换结果集,从而能够创建一个合理的查询(而不是存储过程)将任意SQL查询转换为JSON数组的出色工作。
该查询会将每个数据行编码为带有前导逗号的单个JSON对象。数据行用方括号括起来,然后将整个结果集导出到文件中。
我想看看外面是否有人可以看到改善其性能的方法?
这是带有示例表的查询:
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行。
我的另一个大批评是,它目前假设所有看起来像数字的东西都是数字。它不会尝试最少地发现列类型(而且我什至不确定是否可以)。
最后的次要批评是,第一个数据行将在前面加逗号,从技术上讲,它不应该这样。为了弥补这一点,它需要在启动JSON数组的第一行中使用空的JSON对象。
邀请其他评论(最好是使用解决方案)时,我唯一真正的限制是,该解决方案可以在许多任意SQL查询上重复进行,而不必显式标识列名。
我正在使用SQL Server 2012。
感谢和其他像我一样的人,他们正在寻找广义的SQL结果-> JSON数组转换器,请尽情享受!
我说,如果您真的想提高性能,请使用元编程。下面的示例使用40,000行进行尝试,并在不到一秒的时间内返回结果(不算插入初始的40k行,在此示例中仅需2秒钟左右)。它还考虑到您的数据类型不要将数字用引号引起来。
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查询转换为Elasticsearch之一。谁能帮上忙 我尝试了以下方法: 但不确定我是否做对了,因为它无法验证结果。似乎要在聚合内添加查询。 问题答案: 假设您使用Elasticsearch 2.x,则有可能在Elasticsearch中 具有 -semantics。我不知道2.0之前的可能性。 您可以使用新的Pipeline Aggregation Bucket Se
问题内容: 多亏了Erwin Brandstetter在我之前的问题“具有has_many关系的订单”中的帮助,我的SQL查询才能正常工作。 如何将该SQL转换为ActiveRecords或AREL查询以在范围中使用? 我最近来的是在朋友的帮助下… …这给了我一个错误: 更新: 我之前的问题对相关的架构和查询有完整的描述。但是基本上Articles have_many Metrics和一个Metr
问题内容: 实际上,我有1个查询,但无法将其转换为CakePHP查询格式。 当我将此查询转换为CakePHP时,会出现如下错误: 问题答案: 您可以轻松地在Cake上运行直接SQL查询,例如:$ this-> Picture-> query(“ SELECT * FROM pictures LIMIT 2;”); 或尝试与此类似的东西: ..确保您已正确链接Esl_Userresults和Esl_
我曾尝试使用ApacheFop将HTML转换为PDF。(HTML-- 它适用于简单的html文件。 它不适用于带有样式(通过嵌入的css或样式属性)的html文件。PDF已创建,但完全未格式化。我试图转换超文本标记语言文件,我没有太多的样式/内容控制。 在我的用例中,为每个html创建xslt并不实用。 目前,我确实有一个与flyingsaucer一起工作的实现。但是,该要求要求在没有AGPL许可
我有两个表,它们通过一个外键来维护它们之间的父子关系。查询如下所示。我想在使用jpa的同时使用标准版。所以有人可以帮助我使用标准版吗 表“child”的“notification\u id\u child”列是外键,并引用表“parent”的主键。
我使用了cakephp Mysql到mongodb查询组件,即将Mysql查询转换到mongodb中,但是当查询有多个括号时代码停止工作,我还尝试将http://www.querymongo.com/site上的查询转换为相同的问题,