parsejson - SQL Server SELECT a la función JSON
recorrer un json en sql server (3)
Me gustaría dar salida a los resultados de una instrucción SELECT como un objeto JSON.
Me gustaría que esto sea una función y no un procedimiento almacenado .
Por ejemplo, los usuarios de la tabla siguiente
id name active
1 Bob Jones 1
2 John Smith 0
Sería devuelto así:
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
Gracias por adelantado.
Primero, quiero agradecer a Kirill Polishchuk por la muestra del código esencial ... ¡gracias!
Lo tomé y fui a crear un procedimiento para hacer lo que necesito que haga, y eso es darme una salida JSON basada en "cualquier" conjunto de resultados que quiero, es decir, objeto de tabla (no variable) en SQL Server.
Idealmente, me gustaría tener esto como una función, sin embargo, debido a las limitaciones de lo que puede hacer dentro de una función, esa parte tendrá que esperar ... tal vez v2. :)
Y sí, registrar un procedimiento extendido (CLR, por sus siglas en inglés) definitivamente sería más fácil, pero quería evitar tomar ese camino por el momento.
PD: para tablas temporales, simplemente ponga ''tempdb .. # tablename''
Aquí está:
/*
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
Puede usar for xml path , por ejemplo:
declare @t table(id int, name nvarchar(max), active bit)
insert @t values (1, ''Bob Jones'', 1), (2, ''John Smith'', 0)
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, '''') + '']''
Salida:
[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]
Solo para mejorar la respuesta con los últimos cambios tecnológicos. con el servidor sql 2016
select id, name ,active
from tableName
FOR JSON AUTO