SQL Server SELECT到JSON功能 [英] SQL Server SELECT to JSON function

问题描述

我想将SELECT语句的结果输​​出为JSON对象.

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!

例如，下表用户"

id    name        active
1     Bob Jones   1
2     John Smith  0

将这样返回:

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

谢谢.

推荐答案

从SQL Server 2016开始，您可以使用for json:

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

对于旧版SQL Server，您可以使用for xml path，例如:

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

输出:

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

这篇关于SQL Server SELECT到JSON功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！