How to Output SELECT Query Results as JSON Using SQL Server Functions?

Output SELECT Results as JSON Using SQL Server Functions

Query:

To output the results of a SELECT statement as a JSON object using a function, you can utilize the following approach:

FOR JSON AUTO (SQL Server 2016 ):

SELECT id, name, active
FROM Users
FOR JSON AUTO;

FOR XML PATH (SQL Server Pre-2016):

SELECT '[' + STUFF((
    SELECT
        ',{"id":' + CAST(id AS VARCHAR(MAX))
        + ',"name":"' + name + '"'
        + ',"active":' + CAST(active AS VARCHAR(MAX))
        + '}'
    FROM Users t1
    FOR XML PATH(''), TYPE
).VALUE('.', 'VARCHAR(MAX)'), 1, 1, '') + ']';

Example:

Consider the following "Users" table:

id	name	active
1	Bob Jones	1
2	John Smith	0

The above queries would return the following JSON result:

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

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