How do you retrieve JSON formatted data from SQL Server?

You can use 'FOR JSON AUTO' at the end of your regular SQL Query to retrieve data from the query in JSON format. You can only use this in SQL Server 2016.

Here is an example from AdventureWorks2014 database in SQL Server 2016. The query is as follows. It is a simple query that return 17 rows of data of 'Persons' living in area code 808.

============
SELECT        Person.Person.BusinessEntityID, Person.Person.FirstName, Person.Person.MiddleName, Person.PersonPhone.PhoneNumber
FROM            Person.EmailAddress INNER JOIN
                         Person.Person ON Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID INNER JOIN
                         Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
                         Where PhoneNumber like '808%'
For JSON Auto

===============

The response to this query is as shown here:
==========
[{"BusinessEntityID":1,"FirstName":"Ken","MiddleName":"J","Person.PersonPhone":[{"PhoneNumber":"808-722-6655"}]},{"BusinessEntityID":188,"FirstName":"Douglas","MiddleName":"B","Person.PersonPhone":[{"PhoneNumber":"808-555-0172"}]},{"BusinessEntityID":2766,"FirstName":"Alexandra","MiddleName":"M","Person.PersonPhone":[{"PhoneNumber":"808-555-0111"}]},{"BusinessEntityID":3913,"FirstName":"Abigail","Person.PersonPhone":[{"PhoneNumber":"808-555-0111"}]},{"BusinessEntityID":4933,"FirstName":"Devin","MiddleName":"I","Person.PersonPhone":[{"PhoneNumber":"808-555-0131"}]},{"BusinessEntityID":7754,"FirstName":"Reginald","MiddleName":"C","Person.PersonPhone":[{"PhoneNumber":"808-555-0181"}]},{"BusinessEntityID":7976,"FirstName":"Danielle","Person.PersonPhone":[{"PhoneNumber":"808-555-0114"}]},{"BusinessEntityID":8986,"FirstName":"Bailey","Person.PersonPhone":[{"PhoneNumber":"808-555-0180"}]},{"BusinessEntityID":11434,"FirstName":"Nicole","Person.PersonPhone":[{"PhoneNumber":"808-555-0169"}]},{"BusinessEntityID":12741,"FirstName":"Alexis","MiddleName":"E","Person.PersonPhone":[{"PhoneNumber":"808-555-0189"}]},{"BusinessEntityID":13010,"FirstName":"Emily","Person.PersonPhone":[{"PhoneNumber":"808-555-0115"}]},{"BusinessEntityID":15571,"FirstName":"Adam","MiddleName":"R","Person.PersonPhone":[{"PhoneNumber":"808-555-0157"}]},{"BusinessEntityID":16094,"FirstName":"Jason","Person.PersonPhone":[{"PhoneNumber":"808-555-0157"}]},{"BusinessEntityID":17444,"FirstName":"Thomas","MiddleName":"A","Person.PersonPhone":[{"PhoneNumber":"808-555-0120"}]},{"BusinessEntityID":19244,"FirstName":"Brianna","MiddleName":"C","Person.PersonPhone":[{"PhoneNumber":"808-555-0149"}]},{"BusinessEntityID":19425,"FirstName":"Victoria","MiddleName":"A","Person.PersonPhone":[{"PhoneNumber":"808-555-0135"}]},{"BusinessEntityID":20771,"FirstName":"Isabella","Person.PersonPhone":[{"PhoneNumber":"808-555-0174"}]}]
----------------