Retrieve data as JSON

When we're working with our tables in the database, we might need to convert the results to JSON format. This works very similarly to the XML conversions. SQL Server has a directive for the SELECT command FOR JSON.

You can run the following query to get the JSON document:

SELECT
   [Title]
  ,[FirstName]
  ,[MiddleName]
  ,[LastName]
  ,[Suffix]
  ,[JobTitle]
  ,[PhoneNumber]
  ,[PhoneNumberType]
  ,[EmailAddress]
FROM [AdventureWorks2016].[HumanResources].[vEmployee]
FOR JSON AUTO

The result of such a query would be a JSON set of documents representing each employee record in the following format:

{"FirstName":"Ken","MiddleName":"J","LastName":"Sánchez","JobTitle":"Chief Executive Officer","PhoneNumber":"697-555-0142","PhoneNumberType":"Cell","EmailAddress":"ken0@adventure-works.com"}

Unlike with XML, with JSON, you have only two available modes, which can be used to process the query and transform the result set into JSON output. The modes are as follows:

• FOR JSON PATH
• FOR JSON AUTO

The first one offers you a way to modify the look of the results, which allows you to control how the wrapping and more complex outputs will be produced. With JSON AUTO, SQL Server will automatically format the JSON output based on the structure of the SELECT query you have executed.