How to get SQL SELECT query result in JSON Format

Yes, it is possible to get the data in JSON format from SQL SERVER SELECT query result, but you should have SQL SERVER version 2016 and later installed on your machine. JSON format has become popular and it is widely used in Web APIs to input and output result into JSON format and recently SQL SERVER has introduced lot of support for JSON type data.

First, here we will create Book Table using below Create Table query:

Create Table Book
(
  id int IDENTITY(1,1) PRIMARY KEY,
  title nvarchar(200),
  price int,
  authorname nvarchar(200),
  publishername nvarchar(500)
)

After creation of Book table, we will insert some rows into it using below Query:

INSERT INTO Book VALUES('Introduction to SQL',100,'Wilson Joseph','Standard Publishers');
INSERT INTO Book VALUES('Introduction to Cloud',200,'John Perry','World1 Publishers');

Now, we will do SELECT from Book table using below query and we will get result as follow:

SELECT [id]
      ,[title]
      ,[price]
      ,[authorname]
      ,[publishername]
  FROM [dbo].[Book]

Now, we will get result like Table Data as shown below:

Now, we will see by using "FOR JSON PATH" after the SELECT query will give us JSON result as shown below:

SELECT [id]
      ,[title]
      ,[price]
      ,[authorname]
      ,[publishername]
  FROM [dbo].[Book] FOR JSON PATH

[
   {
      "id":1,
      "title":"Introduction to SQL",
      "price":100,
      "authorname":"Wilson Joseph",
      "publishername":"Standard Publishers"
   },
   {
      "id":2,
      "title":"Introduction to Cloud",
      "price":100,
      "authorname":"John Perry",
      "publishername":"World1 Publishers"
   }

]

I hope you have understood, how to get SQL SELECT query result into JSON format, you just have to add "SELECT * FROM Table_Name FOR JSON PATH;" this will give you result in JSON format.