Do you know, you can return JSON data from a SQL query? It is very useful in writing REST or GraphQL API on the top of a SQL Server Database.

Let us say you have two tables as shown below,

And an emp table as below,

You can query employees with their departments as below,

SELECT empno, 
       ename, job, 
	   mgr,sal,comm,dname, 
	   loc FROM emp, dept
	   WHERE  emp.dept = dept.deptno;

The above query returns you data as below,

This result is very useful but to use then in REST API, you need to manually map them as a JSON data.

However, if you are using SQL Server 2016 and later, you can fetch a query directly in JSON.  You can modify above query to return JSON data as below,

SELECT empno, 
       ename, job, 
	   mgr,sal,comm,dname, 
	   loc FROM emp, dept
	   WHERE  emp.dept = dept.deptno
	   for JSON PATH;

This is very useful when you are connecting SQL Server with Tedious in NodeJS to create a REST API. I hope you find this post useful. Thanks for reading.