All 7 JSON Support in SQL Server questions
More in JSON Support in SQL Server
How do you store and query JSON formatted text in SQL Server? What is the difference between the JSON_VALUE and JSON_QUERY functions? How do you convert a JSON array stored in a column into a relational table format that can be queried like normal rows? How do you update a specific value within a JSON document stored in a column without replacing the entire JSON text? How would you validate that a value stored in a column actually contains well formed JSON before allowing it to be inserted or updated? What are the performance considerations and limitations of querying JSON data stored as text compared to using normalized relational columns?
Advertisement
SQL Server › JSON Support in SQL Server

How do you generate JSON formatted output directly from a SQL query's result set?

Intermediate

Answer

You add the FOR JSON clause to the end of your SELECT statement, choosing either AUTO to let SQL Server automatically structure the output based on your table and column names, or PATH for more explicit control over the exact shape and nesting of the resulting JSON document.
SELECT OrderId, CustomerName, Amount
FROM Orders
FOR JSON PATH;
Real-world example A web API endpoint queries order data directly from SQL Server using FOR JSON PATH, returning properly formatted JSON that can be sent straight to a front end application without any additional conversion step in the application code.

Common follow-ups: What is the difference between FOR JSON AUTO and FOR JSON PATH?;How do you include a root wrapper object around the JSON array output?

Stored Procedures & Functions;Data Types & Schema Design
All 7 JSON Support in SQL Server questions