How can I get a JSON object from a SQL Server table?

Question

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?

Answer 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

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

Answer 3

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.