Convert select query results into Json

Question

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

Here is the table schema

CREATE TABLE TestTable
(

   Id int primary key identity(1,1),
   Name nvarchar(200),
   About nvarchar(2000),
   Age int,
   AddressId int
 )

I need to get values from Id, Name, About and Age into a JSON List

Answer 1

JSON AUTO would make quick work of this in but JSON support is available only in SQL Server 2016 and later, including Azure SQL Database. For a T-SQL solution in SQL 2014 and earlier, you'll need to build the JSON string yourself.

Below is an example that uses a FOR XML subquery to concatenate the result into JSON format and adds the outermost [ and ] for the array. Note that this does not handle characters that must be escaped in JSON (\ and ") so you'll 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,'')
    + ']';

Answer 2

For SQL server 2017

CREATE TABLE mytable(
  ID int PRIMARY KEY,
  Name varchar(50),
  teamName varchar(255),
  Create_Date DATETIME
);

INSERT INTO mytable VALUES (1,NULL,'TEST1','2017-01-02');
INSERT INTO mytable VALUES (2,NULL,'TEST2',NULL);
INSERT INTO mytable VALUES (3,'KK','TEST3','2017-01-02');
INSERT INTO mytable VALUES (4,NULL,NULL,NULL);

Try below way here i provide an example

SELECT 
       ID,  
       Name,  
       teamName,  
       Create_Date 
   FROM mytable 
   FOR JSON AUTO  

http://www.sqlfiddle.com/#!18/81350/1

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
[{"ID":1,"teamName":"TEST1","Create_Date":"2017-01-02T00:00:00"},{"ID":2,"teamName":"TEST2"},{"ID":3,"Name":"KK","teamName":"TEST3","Create_Date":"2017-01-02T00:00:00"},{"ID":4}]

For below vesion of sql 2017 server:

1st create a scaler function

create FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
AS
Begin
    Declare @Head varchar(max) = '',@JSON varchar(max) = ''
    ; with cteEAV as (Select RowNr=Row_Number() over (Order By (Select NULL))
                            ,Entity    = xRow.value('@*[1]','varchar(100)')
                            ,Attribute = xAtt.value('local-name(.)','varchar(100)')
                            ,Value     = xAtt.value('.','varchar(max)') 
                       From  @XML.nodes('/row') As R(xRow) 
                       Cross Apply R.xRow.nodes('./@*') As A(xAtt) )
          ,cteSum as (Select Records=count(Distinct Entity)
                            ,Head = IIF(@IncludeHead=0,IIF(count(Distinct Entity)<=1,'[getResults]','[[getResults]]'),Concat('{"status":{"successful":"true","timestamp":"',Format(GetUTCDate(),'yyyy-MM-dd hh:mm:ss '),'GMT','","rows":"',count(Distinct Entity),'"},"results":[[getResults]]}') ) 
                       From  cteEAV)
          ,cteBld as (Select *
                            ,NewRow=IIF(Lag(Entity,1)  over (Partition By Entity Order By (Select NULL))=Entity,'',',{')
                            ,EndRow=IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}')
                            ,JSON=Concat('"',IIF(@ToLowerCase=1,Lower(Attribute),Attribute),'":','"',Value,'"') 
                       From  cteEAV )
    Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum
    Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))
End
-- Parameter 1: @IncludeHead 1/0
-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase
-- Parameter 3: (Select * From ... for XML RAW)

Then use this function json conversion below query is an example

Declare @Table table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @Table values
(1,1,'John','Smith','[email protected]'),
(2,0,'Jane','Doe'  ,'[email protected]')

Select A.ID
      ,A.Last_Name
      ,A.First_Name
      ,B.JSON
From  @Table A 
Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,1,(Select A.* For XML Raw)) ) B

Answer 3

From SQL-Server 2016+, you can use JSON AUTO to return Json:

Select * From Customers FOR JSON AUTO;

If you expect to return just a single row, then you can add Without_Array_Wrapper after FOR JSON AUTO with a comma after the AUTO keyword, and you will get an object instead of an array:

Select * From Customers FOR JSON AUTO, Without_Array_Wrapper;

Note: If you add the Without_Array_Wrapper and return multiple rows, you will get a newly populated object type but won't get any error - which requires carefulness as you won't know if anything is wrong and might hit you later when exercising the data.

Answer 4

From SQL-Server 2016+, you can use JSON AUTO to return Json:

I know two way to return query in JSON format. If your query is from one table or only return a single table (as usual) you can use JSON PATH or JSON AUTO

CREATE TABLE TestTable
(

   Id int primary key identity(1,1),
   Name nvarchar(200),
   About nvarchar(2000),
   Age int,
   AddressId int
 )

INSERT INTO dbo.TestTable(Name,About,Age,AddressId)      
 VALUES (N'John' , NULL ,21, 16),(N'Mehdi' , 'Developer' ,32, 15)

query is

select * from [TestTable] for JSON PATH

output :

[{
        "Id": 1,
        "Name": "Mehdi",
        "About": "Developer",
        "Age": 32,
        "AddressId": 15
    }, {
        "Id": 3,
        "Name": "John",
        "Age": 21,
        "AddressId": 16
    }
]

Notice that NULL values do not show on result. For showing the null values, [INCLUDE_NULL_VALUES] must be added at the end of the query.

SELECT * FROM [TestTable] WHERE id=2 FOR JSON PATH , INCLUDE_NULL_VALUES

output:

[{
        "Id": 3,
        "Name": "John",
        "About": null,
        "Age": 21,
        "AddressId": 16
    }
]

For the query that only return an object, WITHOUT_ARRAY_WRAPPER must be added at the end of query the brackets are remove from the result.

SELECT * FROM [TestTable] WHERE id=2 FOR JSON PATH , INCLUDE_NULL_VALUES , WITHOUT_ARRAY_WRAPPER 

output : { "Id": 3, "Name": "John", "About": null, "Age": 21, "AddressId": 16 } For adding sub query for example the user's contracts or user's family names.

there two way : 1-

SELECT
  * , 
ContractList = (SELECT* FROM contract where UserId = 2 FOR JSON PATH)
FROM TestTable  FOR JSON PATH

2-

SELECT
  * , 
(JSON_QUERY((SELECT* FROM contract WHERE UserId = 2 FOR JSON PATH))) AS ContractList
FROM TestTable  FOR JSON PATH

output:

 [{
            "Id": 3,
            "Name": "John",
            "About": null,
            "Age": 21,
            "AddressId": 16 , 
            "ContractList" : [{...}, {...}]
        }
 ]
    

Answer 5

Thank you so much, your answer came to my rescue after a week-long search.

Had to make a few changes to suit my scenario and add quotes around the code

SELECT '[' + STUFF((
    SELECT
          ',['
        + '"'+ CAST(s.Code AS varchar(10)) + '"' + ','
        + COALESCE(''+CAST(COUNT(s.ControlNumber) AS varchar(10)), '')
        + ']'
    FROM school s
    INNER JOIN [dbo].[Class] cc ON cc.SchoolID = s.SchoolID 
    INNER JOIN [dbo].[Subject] s ON s.subjectID = cc.subjectID 
    INNER JOIN [dbo].Marks ms ON s.MarkID = c.CaseStatusID 
    WHERE ms.Created BETWEEN '2021-04-01 00:00:00' AND '2021-04-07 23:59:59' 
    AND s.subjectID IN (1, 2, 3, 4, 5, 6, 7, 8, 9) 
    Group By s.Code
    FOR XML PATH(''), TYPE).value('.', 'varchar(MAX)'),1,1,'')
    + ']';

Output

[["za-ec",14],["za-fs",5],["za-gt",3],["za-mp",14],["za-nc",2],["za-nl",8],["za-np",5],["za-nw",6],["za-wc",15]]