You can execute a SQL query against your database and return the results as a JSON document by using the FOR JSON clause in a SELECT statement.
...For JSON mode mode : RAW | AUTO | EXPLICIT
SELECT emp.EmployeeID, so.CustomerID, so.Region FROM Employees AS emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195 ORDER BY 1 FOR JSON RAW;
[ { "EmployeeID" : 129, "CustomerID" : 107, "Region" : "Eastern" }, { "EmployeeID" : 129, "CustomerID" : 119, "Region" : "Western" }, ... { "EmployeeID" : 129, "CustomerID" : 131, "Region" : "Eastern" }, { "EmployeeID" " 195, "CustomerID" : 176, "Region" : "Eastern" } ]
[ { "emp": { "EmployeeID" : 129, "so" : [ { "CustomerID" : 107 , "Region" : "Eastern" }, ... { "CustomerID" : 131 , "Region" : "Eastern" } ] } }, { "emp" : { "EmployeeID" : 195, "so" : [ { "CustomerID" : 109 , "Region" : "Eastern" }, ... { "CustomerID" : 176 , "Region" : "Eastern" } ] } } ]
SELECT 1 AS tag, NULL AS parent, emp.EmployeeID AS [!1!EmployeeID], so.CustomerID AS [!1!CustomerID], so.Region AS [!1!Region] FROM Employees AS emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195 ORDER BY 3 FOR JSON EXPLICIT;
[ { "EmployeeID" : 129, "CustomerID" : 107, "Region" : "Eastern" }, { "EmployeeID" : 129, "CustomerID" : 119, "Region" : "Western" }, ... { "EmployeeID" : 129, "CustomerID" : 131, "Region" : "Eastern" }, { "EmployeeID" " 195, "CustomerID" : 176, "Region" : "Eastern" } ]
SELECT 1 AS tag, NULL AS parent, emp.EmployeeID AS [emp!1!EmployeeID], null AS [so!2!CustomerID], null AS [!2!Region] FROM Employees as emp where emp.EmployeeID <= 195 UNION ALL SELECT 2, 1, emp.EmployeeID, so.CustomerID, so.Region FROM Employees as emp KEY JOIN SalesOrders as so where emp.EmployeeID <= 195 ORDER BY 3, 1 FOR JSON EXPLICIT;
[ {"emp": [{"EmployeeID":102}]}, {"emp":[{"EmployeeID":105}]}, {"emp": [{"EmployeeID":129, "so":[ {"CustomerID":101,"Region":"Eastern"}, ... {"CustomerID":205,"Region":"Eastern"} ] }] }, {"emp":[{"EmployeeID":148}]}, {"emp":[{"EmployeeID":160}]}, {"emp":[{"EmployeeID":184}]}, {"emp":[{"EmployeeID":191}]}, {"emp": [{"EmployeeID":195, "so":[ {"CustomerID":101,"Region":"Eastern"}, ... {"CustomerID":209,"Region":"Western"} ] }] } ]
Besides the ordering of the arrays and the inclusion of employees with no sales orders, the format above differs from the FOR JSON AUTO results only in that emp is an array of structures. In FOR JSON AUTO it is understood that emp only has a single object. FOR JSON EXPLICIT uses an array encapsulation that supports aggregation.
SELECT 1 AS tag, NULL AS parent, emp.EmployeeID AS [!1!EmployeeID], // remove "emp" encapsulation null AS [so!2!id], // change "CustomerID" to just "id" null AS [!2!] // stipulate that region should be emitted as a value FROM Employees AS emp WHERE emp.EmployeeID <= 195 UNION ALL SELECT 2, 1, emp.EmployeeID, so.CustomerID, so.Region FROM Employees as emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195 ORDER BY 3, 1 FOR JSON EXPLICIT;
[ {"EmployeeID":102},{"EmployeeID":105},{"EmployeeID":129, "so":[ [{"id":101},"Eastern"], ... [{"id":205},"Eastern"] ] }, {"EmployeeID":148}, {"EmployeeID":160}, {"EmployeeID":184}, {"EmployeeID":191}, {"EmployeeID":195, "so":[ [{"id":101},"Eastern"], ... [{"id":209},"Western"] ] } ]
SELECT 1 AS tag, NULL AS parent, emp.EmployeeID, // no alias directives so.CustomerID, so.Region FROM Employees AS emp KEY JOIN SalesOrders AS so WHERE emp.EmployeeID <= 195 ORDER BY 3 FOR JSON EXPLICIT;
[ [129,107,"Eastern"], ... [195,176,"Eastern"] ]
The FOR JSON clause can be used in any SELECT statement, including subqueries, queries with a GROUP BY clause or aggregate functions, and view definitions. Using the FOR JSON clause represents relational data as a JSON array composed of arrays, objects, and scalar elements.
The RAWclause is the recommended method for retrieving query results as JSON objects as it is the easiest method to parse and understand.
Use the AUTO clause in a query when you want the result set to show the hierarchical relationship between the JSON objects.
The EXPLICIT clause uses a column alias to provide a detailed format specification. If an alias is not present, then the given column is output as a value. An alias must be present to express a value (or object) within a nested structure. You must name the first two columns in the select-list tag and parent. A union of multiple queries can return nested JSON output by specifying the tag and parent relationship within each query.
[encapsulating_object!tag!name!qualifier]where:
ANSI SQL – Compliance level: Transact-SQL extension.