This article shows how to convert SQL query to JSON in SQL Server database.
In SQL Server, you can use the FOR JSON clause to convert SQL data into JSON format. This feature was introduced in SQL Server 2016 and allows you to generate JSON output from a SELECT statement.

Example

Here's an example of how to use the FOR JSON clause:

FOR JSON clause

Suppose you have a table called "employees" with columns "id", "name", and "salary". To convert the data from this table into JSON format, you can use the following SQL query:

SELECT id, name, salary
FROM employees
FOR JSON AUTO;

In this query, the FOR JSON clause is used to specify the output format as JSON. The AUTO keyword indicates that the output should be automatically generated based on the structure of the SQL query.

The resulting JSON output would look something like this:

[
{"id": 1, "name": "John Doe", "salary": 50000},
{"id": 2, "name": "Jane Smith", "salary": 60000},
{"id": 3, "name": "Bob Johnson", "salary": 70000}
]

JSON_QUERY function

You can also specify a specific output format by using the JSON_QUERY function. For example:

SELECT JSON_QUERY(
(SELECT id, name, salary
FROM employees
FOR JSON PATH),
'$') AS json_output;

This query generates the same JSON output as the previous example, but with a more explicit output format specified using the JSON_QUERY function.

In summary, the FOR JSON clause in SQL Server allows you to easily convert SQL data into JSON format. By using the appropriate output format and JSON functions, you can customize the JSON output to meet your specific needs.