Convert SQL Table Data into JSON format string in MS SQLServer [2 ways]

/ / 0 Comments
Export SQL to JSON: Here in this article we learn how easily we can convert our SQL table data into JSON string. JSON is a lightweight data-interchange format. It is easy for humans to read and write. It is a common data format with diverse uses in electronic data interchange.
Thus the increased popularity of JSON in modern web applications, made the database team to expose some of their data to web application in a JSON format. MS SQL provided two options for converting data into JSON that is by using FOR JSON AUTO, and FOR JSON PATH.

Below are two ways to Convert SQL to JSON:

  1. FOR JSON AUTO: This option automatically creates nested JSON data with sub-arrays based on the table hierarchy used in the SQL Query. Also, the format of the JSON output is automatically determined based on the order of columns in the SELECT. The AUTO option must have a FROM clause.
  2. FOR JSON PATH: Using PATH mode allows us to create wrapper objects and nest complex properties. This means it enables us to define the structure of the output of JSON data using the column names with aliases by using dot-separated column names.
Lets first create Employee table with some columns. Create query as below:
 Create Table Employee (
    Id Int IDENTITY(1,1) NOT NULL, 
    FirstName Varchar(255), 
    LastName Varchar(255), 
    Degisnation Varchar(15), 
    Location Varchar(100)
);
Next we insert some data into our newly created table, which we used to convert into json. Insert query as below

Insert Into Employee (FirstName, LastName, Degisnation, Location ) 
SELECT 'David',   'Miller', 'VP', 'Australia'
UNION ALL
SELECT 'Amit', 'Sarna', 'BA', 'India'
UNION ALL
SELECT 'Rahul', 'Mahte', 'Dev', 'America'
UNION ALL
SELECT 'Sunita', 'Devi', 'CTO', 'India' 

#1: Convert SQL data to JSON using FOR JSON AUTO

Here the SQL query of using for json auto, to convert data into JSON string

SELECT Id, FirstName, LastName, Degisnation, Location 
FROM Employee
FOR JSON Auto

Output:

Here the SQL query of using for json auto, along with root() to convert data into JSON string.

SELECT Id, FirstName, LastName, Degisnation, Location 
FROM Employee
FOR JSON Auto, Root('EmployeeList') 
Output with Auto and Root() option:

#2: Convert SQL data to JSON using FOR JSON PATH

Here the SQL query of using for json path, to convert data into JSON string

SELECT
    Id,
    FirstName AS "EmployeeName.FirstName", 
    LastName AS "EmployeeName.LastName", 
    Degisnation, 
	Location
FROM Employee
FOR JSON PATH 
Output:

Here you can see with the PATH option we can create wrapper objects i.e (EmployeeName) which have nest properties i.e (FirstName,LastName).

Other Reference:

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to thank me? 👳 Buy Me a Coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.


Post Comment

Your email address will not be published. Required fields are marked *

0 Comments