How to retrieve data from SQL Sever in JSON format.

Description:

In this example we explain that how to use JSON in SQL Server 2016.or how to use JSON in SQL Server or retrieve or fetch data from SQL Server in JSON format.

Till now it was tricky in sql to split or use Json , But SQL Server 2016 has provided Inbuilt functions for JSON support.

For exmple if you want to select rows from table in JSON format. You can do it by simple 

syntax:

SELECT EmpName,Salarym

  Email, MONO

FROM HumanResources.vEmployee

WHERE BusinessEntityID in (2, 3)

FOR JSON AUTO;

In auto mode database Engine automatically determine the Json format, if you want to define your format then you can use path mode.

SELECT p.FirstName, p.MiddleName, p.LastName,

  e.BirthDate, e.NationalIDNumber, e.LoginID

FROM HumanResources.Employee e INNER JOIN Person.Person p

  ON e.BusinessEntityID = p.BusinessEntityID

WHERE e.BusinessEntityID in (2, 3)

FOR JSON PATH;