我想将SELECT语句的结果输出为JSON对象。
我希望这是一个函数而不是存储过程！
例如，如下表格中的Users：
id name active
1 Bob Jones 1
2 John Smith 0
就像这样返回：
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
提前感谢。
SQL Server SELECT转JSON函数
44
- jamesmhaley
5
2这并不是仅通过SQL查询就能完成的事情。最好使用某种程序来管理数据库。你用什么编程语言来显示这些数据？ - schizodactyl
http://www.adampresley.com/2010/07/experimenting-with-sql-to-json-in-sql.html提供了使用CLR函数将XML响应解析为JSON的示例。我不知道您是否能在纯T-SQL中找到可行的解决方案。 - Yuck
1你尝试过什么了吗？这看起来更像是一个竞标而不是一个问题... - Adriano Carneiro
我正在将JSON插入到一个表中，然后使用JSON输出它。问题是，我为什么不在服务器端构建JSON，因为它包含在运行存储过程时创建的数据。 - jamesmhaley
1我尝试了http://weblogs.asp.net/thiagosantos/archive/2008/11/17/get-json-from-sql-server.aspx。但它是一个存储过程，没有起作用。 - jamesmhaley
3个回答
75
自 SQL Server 2016 起，您可以使用 for json：
declare @t table(id int, name nvarchar(max), active bit)
insert @t values (1, 'Bob Jones', 1), (2, 'John Smith', 0)
select id, name, active
from @t
for json auto
在旧版的 SQL Server 中，您可以使用 for xml path，例如：
select '[' + STUFF((
select
',{"id":' + cast(id as varchar(max))
+ ',"name":"' + name + '"'
+ ',"active":' + cast(active as varchar(max))
+'}'
from @t t1
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') + ']'
输出：
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
- Kirill Polishchuk
8
@sparkyfied: 我认为你无法解决这个问题。如果你将内容转储到XML并且内容发生更改，那么你仍会遇到相同的问题。 - Yuck
@sparkyfied：我相信这是可能的，但我不知道有什么优势。也许我没有理解你的意图。 - Yuck
@yuck，如上所述：我将JSON保存在表列中，以便稍后输出。我之所以这样做，是因为列中保存的数据将会更改（有时是：{"contentid":1017}，另一次是：{"content":"news","title":"News List"}）。因此，在插入此数据时，我希望动态构建JSON。我开始认为最好只是将JSON构建为字符串。只需创建一个临时表并具有将其转换为JSON的函数即可更容易地完成。我有一个执行相反操作的函数，即JSON到表格。 - jamesmhaley
这是一个很好的答案。看起来2016年终于添加了本地支持: https://msdn.microsoft.com/en-us/library/dn921897.aspx - JosephStyons
1@Eitanmg，如果您使用
for json，则无需转义，如果您使用那种有点hacky的方式使用for xml，则需要转义。 - Kirill Polishchuk显示剩余3条评论
17
仅为了改进答案，使用最新的技术变更。 使用SQL Server 2016。
select id, name ,active
from tableName
FOR JSON AUTO
- Amrik
3
据我所知，FOR JSON支持是在2016年才添加的，是否有更新可让其在2014年使用？ - kerray
可能是在管理工具的更新中添加的。我使用的是SQL Server 2014和Management Studio 12.0.2000.8版本，FOR JSON功能正常。 - matadur
5我的错误 - 我实际上是在测试SQL Server 2016，只不过使用的是Management Studio 2014。 - matadur
12
首先，我要感谢Kirill Polishchuk提供的基础代码样例......谢谢！
我用它来构建一个过程，以便根据“任何”结果集（即SQL Server中的表对象而非变量）为我提供JSON输出。
理想情况下，我希望这是一个函数，但由于函数内部功能的限制，这一部分将有待改进...可能是v2. :)
是的，注册扩展程序（CLR）肯定更容易，但我想暂时避免走这条路线。
附注：对于临时表，只需输入“tempdb..#tablename”。
以下是代码：
我用它来构建一个过程，以便根据“任何”结果集（即SQL Server中的表对象而非变量）为我提供JSON输出。
理想情况下，我希望这是一个函数，但由于函数内部功能的限制，这一部分将有待改进...可能是v2. :)
是的，注册扩展程序（CLR）肯定更容易，但我想暂时避免走这条路线。
附注：对于临时表，只需输入“tempdb..#tablename”。
以下是代码：
/*
Author: Goran Biljetina
Create date: 03/13/2013
Description: consume a table object (not table var), output it as JSON Properties string
*/
/*
--> example run
-- EXEC dbo.JSONreturn @tblObjNameFQ='[database].[schema].[object_name_table]';
*/
CREATE PROCEDURE dbo.JSONreturn
(
@committedRead bit = 0 --> if 1 then committed else uncommitted read
,@debugmode bit = 0 --> if 1 display certain outputs
,@tblObjNameFQ varchar(128) --> fully qualified table object name, i.e. db.schema.object_name
,@stringJSON nvarchar(max) = null OUTPUT
)
AS
BEGIN
if @committedRead=0
begin
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --> evaluate if necessary in test phase
end
else if @committedRead=1
begin
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
end
SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------
if (PATINDEX('%[\.]%',@tblObjNameFQ)<1 AND patindex('%#%',@tblObjNameFQ)<1) OR LEN(@tblObjNameFQ)>(3*128)
begin
PRINT 'table (object) name not fully qualified or invalid!'
RETURN -1
end
declare
@objname varchar(128)
,@dbname varchar(128)
,@schema varchar(128)
,@maxColNum int
,@inc int
,@dqsl_misc varchar(max)
,@dsql_wrapper varchar(max)
,@dsql_what varchar(max)
,@dsql_where varchar(max)
,@dsql_complete varchar(max)
create table #maxColNum (column_id int)
create table #ColPrep (colString varchar(max), column_id int)
create table #JSONoutput (string nvarchar(max))
if patindex('%#%',@tblObjNameFQ)>0
begin
set @objname = (PARSENAME(@tblObjNameFQ,1))
set @dbname = 'tempdb'
end
else if patindex('%#%',@tblObjNameFQ)<1
begin
set @dbname = SUBSTRING(@tblObjNameFQ,1,PATINDEX('%[\.]%',@tblObjNameFQ)-1)
set @objname = convert(varchar,(PARSENAME(@tblObjNameFQ,1)))
set @schema = convert(varchar,(PARSENAME(@tblObjNameFQ,2)))
end
--select @objname[@objname], @dbname[@dbname], @schema[@schema]
--select @dbname+'.'+@schema+'.'+@objname
set @dqsl_misc =
'
select max(column_id)
from '+@dbname+'.sys.columns
where object_id =
(select object_id from '+@dbname+'.sys.objects where type = ''U'' and name like ''%'+@objname+'%'')
'
insert into #maxColNum
exec(@dqsl_misc)
set @maxColNum = (select column_id from #maxColNum)
set @dsql_what = ''
set @dsql_wrapper =
'
select ''['' + STUFF((
select
'',{''+<<REPLACE>>
+''}''
'
set @dsql_where =
'
from '+@dbname+'.'+case when @schema is null then '' else @schema end+'.'+@objname+' t1
for xml path(''''), type
).value(''.'', ''varchar(max)''), 1, 1, '''') + '']''
'
set @dqsl_misc =
'
select ''"''+sysc.name+''": ''
+case
when syst.name like ''%time%'' or syst.collationid is not null then ''"''''+cast(''+sysc.name+'' as varchar(max))+''''",''
when syst.name = ''bit'' then ''''''+cast((case when ''+sysc.name+''=1 then ''''true'''' else ''''false'''' end) as varchar(max))+'''',''
else ''''''+cast(''+sysc.name+'' as varchar(max))+'''',''
end as colString, sysc.column_id
from '+@dbname+'.sys.columns sysc
join '+@dbname+'.sys.systypes syst
on sysc.system_type_id = syst.xtype and syst.xtype <> 240 and syst.name <> ''sysname''
where object_id = (select object_id from '+@dbname+'.sys.objects where type = ''U'' and name like ''%'+@objname+'%'')
order by sysc.column_id
'
insert into #ColPrep
exec(@dqsl_misc)
set @inc = (select MIN(column_id) from #ColPrep)
while @inc<=@maxColNum
begin
set @dsql_what = @dsql_what+(select case
when @inc = @maxColNum then replace(colString,',','')
else colString end
from #ColPrep where column_id = @inc)
set @inc=@inc+1
IF @inc>@maxColNum
set @dsql_what = ''''+@dsql_what+''''
IF @inc>@maxColNum
BREAK
ELSE
CONTINUE
end
set @dsql_complete = REPLACE(@dsql_wrapper,'<<REPLACE>>',@dsql_what)+@dsql_where
insert into #JSONoutput
exec(@dsql_complete)
SET @stringJSON = (Select string from #JSONoutput)
----------------------------------------------------------------------------------------------------------
END
- Goran B.
2
哦，我忘记了要加上，是的，我看到一些漏洞和不一致...但我很快就搞定了，只是想说，“是的，我知道”。 :) - Goran B.
非常好！我知道你知道，但它不能处理日期时间字段，但没关系。这非常有用。 - smoore4
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 4 SQL Server函数基于参数返回不同的SELECT语句
- 332 SQL Server SELECT INTO @variable?
- 7 SQL Server SELECT @VARIABLE = TOP 1
- 3 SQL Server Select语句中的COALESCE函数
- 8 将JSON转换为SQL Server 2016中的表格
- 16 SQL Server表格转换为JSON
- 3 SQL Server - 存储过程中的 Select * vs Select Column
- 156 将Select结果转换为Insert脚本 - SQL Server
- 3 SQL Server Select Distinct
- 5 在Sql Server中将Json转换为表格