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.