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
From SQL-Server 2016+, you can use
JSON AUTO
to returnJson
:
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.
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" : [{...}, {...}]
}
]
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,'')
+ ']';
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With