SQL Server SELECT to JSON function

Question

I would like to output the results of a SELECT statement as a JSON object.

I would like this to be a Function and not a stored procedure!

For example, the below table Users

id    name        active
1     Bob Jones   1
2     John Smith  0

Would be returned like this:

[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]

Thanks in advance.

Solution 1

Starting from SQL Server 2016 you can use 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

With older versions of SQL Server you can use for xml path, e.g.:

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, '') + ']'

Output:

[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]

Solution 2

Just for improving answer with latest technology change. with sql server 2016

select id, name ,active 
    from  tableName 
      FOR JSON AUTO

Solution 3

So first off, I want to thank Kirill Polishchuk for the essential code sample ... thank you!

I took that and went to build a procedure to do what I need it to do, and that is to give me a JSON output based on "any" result set I want i.e. table object (not variable) in SQL Server.

Ideally, I would want this as a function, however due to limitations on what you can do within a function, that part will have to wait ... maybe v2. :)

And yes, registering an extended procedure (CLR) definitely be easier, but I wanted to avoid going that route for the time being.

PS: for temp tables, just put in 'tempdb..#tablename'

Here it is:

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

Comments

[schizodactyl]

This isn't really something that is appropriate to do with just a SQL query. It makes more sense to do it with some sort of program that sits in front of your database. What programming language are you using to display this data.

[Yuck]

adampresley.com/2010/07/… has an example of doing so with a CLR function to parse an XML response into JSON. I don't know if you will find a viable solution in pure T-SQL.

[Adriano Carneiro]

And... what have you tried so far? This looks more like a bid than a question...

[jamesmhaley]

I'm inserting JSON into a table for it then to be output using JSON. The problem is, and why i'm not building the JSON serverside, is it contains data that is created when running the storeprocedure.

[jamesmhaley]

I have tried weblogs.asp.net/thiagosantos/archive/2008/11/17/…. But it is a stored procdure and didnt work

[jamesmhaley]

I like what you have done here. But the contents of the table could change. One time it could be id,name,active. Another time it could be id,name,email,date etc..

[Yuck]

@sparkyfied: I don't think you can help that. You'll have the same issues if you're dumping to XML and the contents change.

[jamesmhaley]

@yuck, would it be possible to do this in a loop of the columns outputting the column name and the value (not wanting a code fragment, just want to know if it is possible)

[Yuck]

@sparkyfied: I'm sure it would be possible but I don't see what the advantage would be. Maybe I'm missing your intent.

[jamesmhaley]

@yuck, as mentioned above: i'm saving the JSON in a table column to then be output later. I have done it this way because the data the column holds will change (one time its: {"contentid":1017}, another time it is: {"content":"news","title":"News List"}). So when inserting this data, i want to dynamically build the JSON. I'm starting the think it may be best to just build the JSON as a string. Just would be easier to create a temp table and have a function that converts that to JSON. I have a function that does the opposite, JSON to Table.

[Goran B.]

ohh i forgot to add that yes, i see some bugs and inconsistencies ... but I cranked this out pretty fast, but I just thought I say, "yeah I know". :)

[smoore4]

Very nice! I know you know, but it doesn't handle datetime fields, but that is OK. This is very useful.

[JosephStyons]

This is a great answer. Looks like 2016 finally added native support : msdn.microsoft.com/en-us/library/dn921897.aspx

[kerray]

@matadur as far as I can see, FOR JSON support was only added in 2016, is there some update for 2014 to allow it?

[matadur]

Perhaps it was added in an update to Management Studio. I have SQL Server 2014 with Management Studio 12.0.2000.8, and the FOR JSON works.

[matadur]

My mistake - it WAS actually SQL Server 2016 that I was testing against; it was just Mgmt Studio 2014.

[Eitanmg]

does it handle characters that must be escaped in JSON (\ and ")? Or I need to escape it?

[Kirill Polishchuk]

@Eitanmg, if you use for json then you don't need to escape, if you use that hacky way with for xml then you need to escape