How to Convert SQL Data to JSON

In this article, we will learn how to convert sql data to json

Syntax :-

SELECT *
FROM table_name
FOR JSON AUTO;

Let’s get started with creating table.

First create one table like below

CREATE TABLE Employee (
    EmpID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Now insert some data in Employee table like below

INSERT INTO Employee
VALUES 
    (1, 'Parekh', 'Priyank', 'Adajan', 'Surat'),
    (2, 'Shah', 'Vasu', 'Andheri', 'Mumbai'),
    (3, 'Parekh', 'Vrunda', 'Akota', 'Baroda'),
    (4, 'Mistry', 'Parth', 'Ambawadi', 'Ahmedabad'),
    (5, 'Parekh', 'Raj', 'Malviya Nagar', 'Delhi');

Now execute the below query

select * from Employee

The output looks like this.

Now, let’s add FOR JASON PATH clause at the end of the SQL query.

SELECT EmpID
      ,LastName
      ,FirstName
      ,Address
      ,City
FROM Employee
FOR JSON PATH;

Output of JSON

[{“EmpID”:1,”LastName”:”Parekh”,”FirstName”:”Priyank”,”Address”:”Adajan”,”City”:”Surat”},{“EmpID”:2,”LastName”:”Shah”,”FirstName”:”Vasu”,”Address”:”Andheri”,”City”:”Mumbai”},{“EmpID”:3,”LastName”:”Parekh”,”FirstName”:”Vrunda”,”Address”:”Akota”,”City”:”Baroda”},{“EmpID”:4,”LastName”:”Mistry”,”FirstName”:”Parth”,”Address”:”Ambawadi”,”City”:”Ahmedabad”},{“EmpID”:5,”LastName”:”Parekh”,”FirstName”:”Raj”,”Address”:”Malviya Nagar”,”City”:”Delhi”}]

Hope you understand the article , If you still have any questions or queries then please let me know in the comment section, I’ll respond to you as soon as possible.