如何从SQL Server表中获取JSON对象?

-- 
-- Author:      Thiago R. Santos                                           --
-- Create date: Aug 3rd 2008                                                   --
-- Description: Returns the contents of a given table                      --
--              in JavaScript Object Notation.                             --
-- Params:                                                                 --
--      @table_name: the table to execute the query                        --
--      @registries_per_request: equivalent to "select top N * from table" 
-- 
--                               replcing N by the actual number           
-- Influenced by Thomas Frank's post MySQL to JSON @ January 23, 2007      --
-- Post Url: http://www.thomasfrank.se/mysql_to_json.html                  --



create procedure [dbo].[GetJSON]
(
@table_name varchar(50),
@registries_per_request smallint = null
)
as
begin
if((select count(*) from information_schema.tables where table_name =   @table_name)     > 0)
begin
    declare @json varchar(max),
            @line varchar(max),
            @columns varchar(max),
            @sql nvarchar(max),
            @columnNavigator varchar(50),
            @counter tinyint,
            @size varchar(10)

    if (@registries_per_request is null) 
    begin
        set @size = ''
    end
    else 
    begin
        set @size = 'top ' + convert(varchar, @registries_per_request)
    end
    set @columns = '{'

    declare schemaCursor cursor
    for select column_name from information_schema.columns where table_name = @table_name
    open    schemaCursor    

    fetch next from schemaCursor
    into  @columnNavigator

    select  @counter = count(*) from information_schema.columns where table_name = @table_name

    while @@fetch_status = 0
    begin
        set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + '''''''
        set @counter = @counter - 1
        if(0 != @counter) 
        begin
            set @columns = @columns + ','
        end

        fetch next from schemaCursor
        into  @columnNavigator
    end 

    set @columns =  @columns + '}'

    close       schemaCursor
    deallocate  schemaCursor

    set @json = '['

    set @sql = 'select  ' + @size + '''' + @columns + ''' as json into tmpJsonTable from ' + @table_name
    exec sp_sqlexec @sql

    select  @counter = count(*) from tmpJsonTable

    declare tmpCur cursor
    for     select * from tmpJsonTable
    open    tmpCur

    fetch next from tmpCur
    into  @line

    while @@fetch_status = 0
    begin
        set @counter = @counter - 1
        set @json = @json + @line
        if ( 0 != @counter ) 
        begin
            set @json = @json + ','
        end

        fetch next from tmpCur
        into  @line
    end

    set @json = @json + ']'

    close       tmpCur
    deallocate  tmpCur
    drop table  tmpJsonTable

    select @json as json
end
end

我想这是可以做到的,但这似乎是一个非常冗长和容易出错的方式来实现预期的结果。

仍然 支持将数据库序列化为XML,然后使用XSLT将XML转换为JSON,因为XML非常复杂 很 更易于在服务器上使用。

游戏的名字是分离关注点。

以下版本是对该概念的总体重新设计。如果我遗漏了什么,请添加注释,我将进行编辑以进行调整。

--
-- Author:      Matthew D. Erwin (Snaptech, LLC)
-- Create date: May 9, 2013                                                
-- Description: Returns the contents of a given table                      
--              in JavaScript Object Notation JSON - 
--
--              Very notably useful for generating MOCK .json files
--              for testing or before RESTful services are completed.
--
--              This implementation:
--                  *removed cursor (using FOR XML PATH(''))
--                  *properly supports NULL vs quoted values
--                  *supports dates in ISO 8601 - presuming UTC
--                  *uses Data_Type and Is_Nullable info
--                  *escapes '\'
--                  *formats output with tabs/newlines
--                  *can return final results as XML to bypass
--                   truncation in SSMS
--                  *supports schema (e.g. [dbo].[TableName]
--                  *includes "recordCount" field
-- Options:                                                                
--      @table_name: the table to execute the query                        
--      @limit: equivalent to "select top N * from table" 
--      @ssms: flag to use if executing in Sql Server Management Studio
--             to bypass result truncation limits.
-- 
-- Inspired primarily by the 2008 work of Thiago R. Santos which was influenced by Thomas Frank.
-- Usage: [dbo].[GetJSON] @Table_name = 'MySchema.MyTable', @limit = 50, @ssms = 0

create procedure [dbo].[GetJSON] (
    @table_name varchar(max), 
    @limit int = null,
    @ssms bit = 0
)
as
begin
        declare @json varchar(max), @query varchar(max), @table_schema varchar(max) = null
if( charindex('.', @table_name) > 0 )
begin
    set @table_schema = replace(replace( substring(@table_name, 0, charindex('.',@table_name)), '[', ''), ']', '')
    set @table_name = replace(replace( substring(@table_name, charindex('.',@table_name) + 1,len(@table_name)), '[', ''), ']', '')
end

set @query = 
    'select ' + case when @limit is not null then 'top ' + cast(@limit as varchar(32)) + ' ' else '' end + '''{ '' + REVERSE(STUFF(REVERSE(''' +
    CAST((SELECT ' "' + column_name + '" : ' + 
        case when is_nullable = 'YES' 
            then ''' + case when [' + column_name + '] is null then ''null'' else ' + 
                case when data_type like '%char%' or data_type like '%text%' then '''"'' + ' else '' end + 
                case when data_type like '%date%' then 'convert(varchar(23),[' + column_name + '], 126) + ''Z''' else 
                'replace(replace(replace(replace(cast([' + column_name + '] as varchar(max)),''\'',''\\''),''"'',''\"''),char(10),''\n''),char(13),''\n'') ' end + 
                case when data_type like '%char%' or data_type like '%text%' then '+ ''"''' else '' end + ' end + ''' 
            else 
                case when data_type like '%char%' or data_type like '%text%' then '"' else '' end + 
                ''' + ' +
                case when data_type like '%date%' then 'convert(varchar(23),[' + column_name + '], 126) + ''Z' else 
                'replace(replace(replace(replace(cast([' + column_name + '] as varchar(max)),''\'',''\\''),''"'',''\"''),char(10),''\n''),char(13),''\n'') + ''' end +
                case when data_type like '%char%' or data_type like '%text%' then '"' else '' end end + ',' AS [text()] 
                from information_schema.columns where table_name = @table_name and (@table_schema is null or table_schema = @table_schema) FOR XML PATH('') ) as varchar(max)) +
                '''),1,1,'''')) + '' }'' as json into tmpJsonTable from ' + @table_name + ' with(nolock) '
exec sp_sqlexec @query

set @json = 
    '{' + char(10) + char(9) +
    '"recordCount" : ' + Cast((select count(*) from tmpJsonTable) as varchar(32)) + ',' + char(10) + char(9) +
    '"records" : ' + char(10) + char(9) + char(9) + '[' + char(10)
    + REVERSE(STUFF(REVERSE(CAST((SELECT char(9) + char(9) + json + ',' + char(10) AS [text()] FROM tmpJsonTable FOR XML PATH('')) AS varchar(max))),1,2,''))
    + char(10) + char(9) + char(9) + ']' + char(10) + '}'

drop table tmpJsonTable
if( @ssms = 1 and len(@json) > 65535 ) --deal with Sql Server Management Studio text/grid truncation
    select cast('<json><![CDATA[' + @json + ']]></json>' as xml) as jsonString
else
    select @json as jsonString
end

UNPIVOT answer ,避免使用游标和SELECT INTO tempoary表格。

不是为了让OP的问题脱轨,但我想知道在SQL中这样做是否是最好/最合适的方法?在我看来,这可能更容易/更有效地在代码中完成。

起初我也在想同样的事情(这就是我如何找到这篇文章的),但在仔细思考了几分钟后,似乎使用一个实用程序/扩展方法来实现这一点可能会更好;返回生成的JSON字符串。