SQL Server SELECT 到 JSON 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6818441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server SELECT to JSON function
提问by jamesmhaley
I would like to output the results of a SELECTstatement as a JSON object.
我想将SELECT语句的结果输出为 JSON 对象。
I would like this to be a Functionand not a stored procedure!
我希望这是一个函数而不是一个存储过程！
For example, the below table Users
例如，下表用户
id name active
1 Bob Jones 1
2 John Smith 0
Would be returned like this:
会像这样返回：
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
Thanks in advance.
提前致谢。
回答by Kirill Polishchuk
Starting from SQL Server 2016 you can use for json:
从 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
With older versions of SQL Server you can use for xml path, e.g.:
对于旧版本的 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, '') + ']'
Output:
输出：
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
回答by Amrik
Just for improving answer with latest technology change. with sql server 2016
只是为了通过最新的技术变化来改进答案。使用 SQL Server 2016
select id, name ,active
from tableName
FOR JSON AUTO
回答by Goran B.
So first off, I want to thank Kirill Polishchuk for the essential code sample ... thank you!
首先，我要感谢 Kirill Polishchuk 提供的基本代码示例……谢谢！
I took that and went to build a procedure to do what I need it to do, and that is to give me a JSON output based on "any" result set I want i.e. table object (not variable) in SQL Server.
我拿着它去构建一个过程来做我需要它做的事情，那就是给我一个基于“任何”结果集的 JSON 输出我想要，即 SQL Server 中的表对象（非变量）。
Ideally, I would want this as a function, however due to limitations on what you can do within a function, that part will have to wait ... maybe v2. :)
理想情况下，我希望将其作为一个函数，但是由于您可以在函数中执行的操作的限制，该部分将不得不等待...... :)
And yes, registering an extended procedure (CLR) definitely be easier, but I wanted to avoid going that route for the time being.
是的，注册扩展过程 (CLR) 肯定更容易，但我想暂时避免走这条路。
PS: for temp tables, just put in 'tempdb..#tablename'
PS：对于临时表，只需放入'tempdb..#tablename'
Here it is:
这里是：
/*
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
