Learning how to return JSON with SQL Server

Since SQL Server 2016, Microsoft has introduced support for the JSON format to meet the needs of certain specific applications, for which the row/column/value format is too limiting.

SQL Server therefore offers built-in functions to manipulate and generate JSON.

Here's how to return a JSON object (or array), directly from a SQL query:

Using FOR JSON

The FOR JSON clause allows you to format the results of a SQL query as JSON.

It has two modes: AUTO and PATH.

FOR JSON AUTO

This mode automatically generates JSON based on the structure of the SQL query, using the implicit relationships between tables.

Example for a query like this one:

SELECT id, nom, age
FROM Employes
FOR JSON AUTO;

The output will be:

[
  { "id": 1, "nom": "Dupont", "age": 30 },
  { "id": 2, "nom": "Martin", "age": 25 }
]

FOR JSON PATH

This mode gives more flexibility by allowing you to define custom paths and manually structure the JSON hierarchy.

SELECT 
	id AS "employe.id", 
	nom AS "employe.nom", 
	age AS "employe.age"
FROM Employes
FOR JSON PATH, ROOT('employes');

This will produce a nested JSON structure:

{
  "employes": [
    { "employe": { "id": 1, "nom": "Dupont", "age": 30 }},
    { "employe": { "id": 2, "nom": "Martin", "age": 25 }}
  ]
}

Conclusion

As with most SQL DBMSs, it is possible to return JSON objects and arrays directly from the query, and all very simply thanks to native commands!

In this case, FOR JSON for SQL Server!