How to make JSON from SQL query in MS SQL 2014
Just for fun, I created a scalar function based off of my prior answer.
Aside from the obvious XML parameter, I added two additional: 1) Include Header (illustrated below), and 2) ToLower case (I prefer my JSON field names in lower case which links to my classes and such).
If the query is more than one record, a formatted array will be returned.
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','john.smith@email.com'),(2,0,'Jane','Doe' ,'jane.doe@email.com')Select A.ID ,A.Last_Name ,A.First_Name ,B.JSONFrom @Table A Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,1,(Select A.* For XML Raw)) ) B
Returns
ID Last_Name First_Name JSON1 Smith John {"id":"1","active":"1","first_name":"John","last_name":"Smith","email":"john.smith@email.com"}2 Doe Jane {"id":"2","active":"0","first_name":"Jane","last_name":"Doe","email":"jane.doe@email.com"}
Or even more simply
Select JSON=[dbo].[udf-Str-JSON](0,1,(Select * From @Table for XML RAW))
Returns with Header ON
{ "status": { "successful": "true", "timestamp": "2016-10-09 06:08:16 GMT", "rows": "2" }, "results": [{ "id": "1", "active": "1", "first_name": "John", "last_name": "Smith", "email": "john.smith@email.com" }, { "id": "2", "active": "0", "first_name": "Jane", "last_name": "Doe", "email": "jane.doe@email.com" }]}
Returns with Header Off
[{ "id": "1", "active": "1", "first_name": "John", "last_name": "Smith", "email": "john.smith@email.com"}, { "id": "2", "active": "0", "first_name": "Jane", "last_name": "Doe", "email": "jane.doe@email.com"}]
The UDF
ALTER FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)Returns varchar(max)ASBegin 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)
**EDIT - Corrected Typo
The following should create the JSON array for just about any data set. However, I have not created a way to convert bit to true/false yet.
Just one point to consider: The FIRST column in the initial SELECT has to be the Primary Key which is equates to the ENTITY field. In this case, Select * from @User for XML RAW
... ID is the Entity and just so happens to be the first field in the table
As far as performance, 500 records with 19 fields creates a JSON string 191,987 bytes in 0.694 seconds (50 records in 0.098 seconds)
Consider the following:
Declare @User table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50),LastOn DateTime)Insert into @User values(1,1,'John','Smith','john.smith@email.com','2016-10-05 17:32:41.903'),(2,0,'Jane','Doe' ,'jane.doe@email.com','2016-10-05 08:25:18.203')Declare @XML xml = (Select * From @User for XML RAW)Declare @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 A(xRow) Cross Apply A.xRow.nodes('./@*') As B(xAtt) ) ,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('"',Attribute,'":','"',Value,'"') From cteEAV )Select @JSON = @JSON+NewRow+JSON+EndRow From cteBld Select '['+Stuff(@JSON,1,1,'')+']'
Returns
[{"ID":1, "Active":1, "First_Name":"John", "Last_Name":"Smith", "EMail":"john.smith@email.com", "LastOn":"2016-10-05T17:32:41.903", "TotalSales":25569.0000} ,{"ID":2, "Active":0, "First_Name":"Jane", "Last_Name":"Doe", "EMail":"jane.doe@email.com", "LastOn":"2016-10-05T08:25:18.203", "TotalSales":22888.0000}]
A more readable version
cteEAV will dynamically unpivot the data and generate the following:
cteBLD will extend and add flags New/End Row
The Final Select
This will put it all together and generate one final string which can be wrapped or nested as you please.