FOR JSON AUTO

Use the FOR JSON AUTO clause in a query when you want the result set to show the hierarchical relationship between the JSON objects.

The following example returns a JSON array of emp objects, each of which contains an EmployeeID, and an so object. The so object is an array of objects composed of a CustomerID and Region.

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 AUTO;

Unlike FOR JSON RAW, using FOR JSON AUTO returns a nested hierarchy of data, where an emp or Employee object is composed of an so or SalesOrders object that contains an array of CustomerID data:

[
   { "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 statement
• ARRAY constructor [Composite]