Функция SELECT для SQL Server для JSON
Я хотел бы выводить результаты оператора 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}]
Спасибо заранее.
Ответы
Ответ 1
Начиная с 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}]
Ответ 2
Просто для улучшения ответа с последними изменениями в технологии. с сервером sql 2016
select id, name ,active
from tableName
FOR JSON AUTO
Ответ 3
Итак, во-первых, я хочу поблагодарить Кирилла Полищука за простейший образец кода... спасибо!
Я взял это и пошел, чтобы построить процедуру, чтобы сделать то, что мне нужно, чтобы это сделать, и это дать мне вывод JSON, основанный на "любом" наборе результатов, который я хочу, т.е. объект таблицы (не переменный) в SQL Server.
В идеале, я хотел бы, чтобы это как функция, однако из-за ограничений на то, что вы можете делать внутри функции, эта часть должна будет ждать... может быть, v2.:)
И да, регистрация расширенной процедуры (CLR) определенно будет проще, но я пока не хотел идти на этот маршрут.
PS: для временных таблиц просто введите "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+'.'[email protected]+'.'[email protected]
set @dqsl_misc =
'
select max(column_id)
from '[email protected]+'.sys.columns
where object_id =
(select object_id from '[email protected]+'.sys.objects where type = ''U'' and name like ''%'[email protected]+'%'')
'
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 '[email protected]+'.'+case when @schema is null then '' else @schema end+'.'[email protected]+' 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 '[email protected]+'.sys.columns sysc
join '[email protected]+'.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 '[email protected]+'.sys.objects where type = ''U'' and name like ''%'[email protected]+'%'')
order by sysc.column_id
'
insert into #ColPrep
exec(@dqsl_misc)
set @inc = (select MIN(column_id) from #ColPrep)
while @inc<[email protected]
begin
set @dsql_what = @dsql_what+(select case
when @inc = @maxColNum then replace(colString,',','')
else colString end
from #ColPrep where column_id = @inc)
set @[email protected]+1
IF @inc>@maxColNum
set @dsql_what = ''''[email protected]_what+''''
IF @inc>@maxColNum
BREAK
ELSE
CONTINUE
end
set @dsql_complete = REPLACE(@dsql_wrapper,'<<REPLACE>>',@dsql_what)[email protected]_where
insert into #JSONoutput
exec(@dsql_complete)
SET @stringJSON = (Select string from #JSONoutput)
----------------------------------------------------------------------------------------------------------
END
