How to Convert SQL SELECT Results to JSON Using SQL Server Functions?

Convert SELECT Results to JSON Using SQL Server Functions

Question:

How can I convert the results of a SQL SELECT statement into a JSON object using a function, rather than a stored procedure?

Answer:

SQL Server 2016 or Later:

Utilize the FOR JSON AUTO clause following your SELECT statement:

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

SQL Server Versions Before 2016:

Employ the FOR XML PATH() function in conjunction with STUFF():

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

The above is the detailed content of How to Convert SQL SELECT Results to JSON Using SQL Server Functions?. For more information, please follow other related articles on the PHP Chinese website!