How can I get a JSON object from a SQL Server tabl

问题:

I have a view that I want to be converted into JSON. What is the SQL that I can use to produce on the server the JSON string needed to be returned?

回答1:

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

回答2:

I imagine this can be done, but it seems like an extremely long-winded and error-prone way of achieving the desired result.

If I were you I'd break down the problem into look at the ORM technology of your middle tier framework (ASP.NET I assume?) and then serialise to JSON again from the framework. Failing framework support (i.e. you aren't in .NET 3+) I'd still favour serialising the database to XML and then XSLT transforming the XML to JSON since XML is much much easier to work with on the server.

The name of the game is separation of concerns.

回答3:

Below version is a total re-design of this concept. If I've missed something please add a note and I'll edit to adjust.

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

回答4:

jlech answer is OK, but I don't see why you cannot generate directly off a VIEW's metadata using a technique similar to the one in this UNPIVOT answer, avoiding CURSORs and a SELECT INTO tempoary table.

回答5:

Not to derail the OP's question, but I am wondering if doing this in SQL is the best / most appropriate route to take? It seems to me that this might be more easily / effectively done in code.

I was initially wondering the same thing (which is how I found this post), but after chewing on the idea for a few minutes, it seems like this might be better accomplished using a utility / extension method that takes a dataset & returns the resulting JSON string.

Granted, the OP may have good reasons for needing to go this route. I'm just thinking (typing) out loud here...