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.

Answer 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}] 

Answer 2

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

select id, name ,active      from  tableName        FOR JSON AUTO