SQL Server SELECT to JSON 함수

SQL Server SELECT to JSON 함수

A의 결과를 출력하고 싶다.SELECTJSON 오브젝트로서의 스테이트먼트.

저장 프로시저가 아닌 함수였으면 합니다!

예를 들어, 다음 표 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 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}]

최신 테크놀로지의 변경에 의한 회답의 향상을 위해서만.sql server 2016 사용

select id, name ,active 
    from  tableName 
      FOR JSON AUTO

먼저 Kirill Blashchuk에게 필수 코드 샘플을 주셔서 감사합니다.감사합니다!

필요한 작업을 수행하기 위한 절차를 작성했습니다.즉, SQL Server에서 원하는 "임의의" 결과 세트(변수가 아닌 테이블 객체)를 기반으로 JSON 출력을 제공합니다.

이상적으로는 이 기능을 함수로 사용하고 싶지만 함수 내에서 수행할 수 있는 작업에 제한이 있기 때문에 해당 부품은 대기해야 합니다...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+'.'+@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

언급URL : https://stackoverflow.com/questions/6818441/sql-server-select-to-json-function