How can I convert select query results into Json?

Question

I have a table where I need to execute a select query. I need the output in a JSON format or in an excel sheet. I want to do this using query only and I'm using SQL Server 2014.

I have listed my table schema down below:

CREATE TABLE TestTable

(

   Id int primary key identity(1,1),

   Name nvarchar(200),

   About nvarchar(2000),

   Age int,

   AddressId int

 )

I want to fetch values from Id, Name, About and Age into a JSON List.

Please help me out with this?

Answer 1

You can use JSON AUTO for instant result but JSON support is available only in SQL Server 2016 and further versions, including Azure SQL Database. For a T-SQL solution in SQL 2014 and earlier, you have to build the JSON string from your own.

I have listed the example down below that uses a FOR XML subquery to concatenate the result into JSON format and adds the outermost [ and ] for the array. But it does not handle characters that must be escaped in JSON (\ and ") so you need to use REPLACE to escape those if contained in your data.

SELECT '[' + STUFF((

    SELECT

          ',{'

        + '"Id":'+CAST(Id AS varchar(10)) + ','

        + COALESCE('"Name":"' + Name + '",','')

        + COALESCE('"About":"' + About + '",','')

        + COALESCE('"Age":'+CAST(Age AS varchar(10)) + ',','')

        + COALESCE('"AddressId":'+CAST(AddressId AS varchar(10)), '')

        + '}'

    FROM TestTable

    FOR XML PATH(''), TYPE).value('.', 'varchar(MAX)'),1,1,'')

    + ']';

I hope this will help you.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.