[SQL] JSON 함수에 SQL 서버 SELECT

JSON 함수에 SQL 서버 SELECT

나는 출력에 JSON 오브젝트로 SELECT 문의 결과를 싶습니다.

나는 이것이 기능이 아닌 저장 프로 시저 싶습니다!

예를 들어, 아래 표 사용자

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. ==============================

   1.당신이 JSON에 사용할 수있는 SQL 서버 2016에 시작 :

   당신이 JSON에 사용할 수있는 SQL 서버 2016에 시작 :

   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 서버의 이전 버전을 사용하면 XML 경로, 예컨대에 사용할 수 :

   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. ==============================

   2.그냥 최신 기술 변화에 대답을 개선. SQL 서버 2016와

   그냥 최신 기술 변화에 대답을 개선. SQL 서버 2016와

   select id, name ,active 
       from  tableName 
         FOR JSON AUTO
   

3. ==============================

   3.그래서 우선, 내가 ... 필수 코드 샘플에 대한 키릴 츄크 감사드립니다 감사합니다!

   그래서 우선, 내가 ... 필수 코드 샘플에 대한 키릴 츄크 감사드립니다 감사합니다!

   나는 것을 가져다가 내가해야 할 일 할 수있는 절차를 구축했다, 그 날을 기반으로 JSON 출력을 제공하는 것입니다 "어떤"결과는 내가 SQL 서버에서 (가변되지 않음) 즉, 테이블 개체를 원하는 설정합니다.

   이상적으로, 나는 그러나 때문에 당신이 함수 내에서 할 수있는 일에 한계로, 함수로이 원하는 것, 그 부분은 아마 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
   

from https://stackoverflow.com/questions/6818441/sql-server-select-to-json-function by cc-by-sa and MIT license