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:
- 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.
- 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.
Create Table Employee ( Id Int IDENTITY(1,1) NOT NULL, FirstName Varchar(255), LastName Varchar(255), Degisnation Varchar(15), Location Varchar(100) );
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
SELECT Id, FirstName, LastName, Degisnation, Location FROM Employee FOR JSON Auto
SELECT Id, FirstName, LastName, Degisnation, Location FROM Employee FOR JSON Auto, Root('EmployeeList')
#2: Convert SQL data to JSON using FOR JSON PATH
SELECT Id, FirstName AS "EmployeeName.FirstName", LastName AS "EmployeeName.LastName", Degisnation, Location FROM Employee FOR JSON PATH
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