How to make JSON from SQL query in MS SQL 2014

Question

Question: What is best solution to generate JSON from a SQL query in MS SQL 2014? I created a procedure, but it is very slow.

My Example:

DECLARE @customers xml;
DECLARE @json NVARCHAR(max);
SET @customers  =  (SELECT * FROM dbo.Customers FOR XML path, root)
EXEC [dbo].[HTTP_JSON]  @customers, @json

EXEC [dbo].[HTTP_JSON](@Shopping)

Create PROCEDURE [dbo].[HTTP_JSON]
@parameters xml, @response NVARCHAR(max) OUTPUT
WITH EXEC AS CALLER
AS
set @response = (SELECT Stuff(  
  (SELECT * from  
    (SELECT ',
    {'+  
      Stuff((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+
                    b.c.value('text()[1]','NVARCHAR(MAX)') +'"'

             from x.a.nodes('*') b(c)  
             for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
        ,1,1,'')+'}' 
   from @parameters.nodes('/root/*') x(a)  
   ) JSON(theLine)  
  for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,''))
GO

With SQL Server 2016 there is native JSON support... For this question you do not provide enough details: Please show your input. Provide an example of @parameters and provide the expected output (example of a valid JSON built with the actual data from @parameters) — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 5, 2016 at 20:51

Jamie Marshall · Accepted Answer · 2018-06-16 02:13:02Z

17

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','[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

Returns

ID  Last_Name   First_Name  JSON
1   Smith       John        {"id":"1","active":"1","first_name":"John","last_name":"Smith","email":"[email protected]"}
2   Doe         Jane        {"id":"2","active":"0","first_name":"Jane","last_name":"Doe","email":"[email protected]"}

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": "[email protected]"
    }, {
        "id": "2",
        "active": "0",
        "first_name": "Jane",
        "last_name": "Doe",
        "email": "[email protected]"
    }]
}

Returns with Header Off

[{
    "id": "1",
    "active": "1",
    "first_name": "John",
    "last_name": "Smith",
    "email": "[email protected]"
}, {
    "id": "2",
    "active": "0",
    "first_name": "Jane",
    "last_name": "Doe",
    "email": "[email protected]"
}]

The UDF

ALTER 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)

**EDIT - Corrected Typo

edited Jun 16, 2018 at 2:13

Jamie Marshall

2,3865 gold badges38 silver badges65 bronze badges

answered Oct 9, 2016 at 18:16

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ramesh Rajendran Over a year ago

It's not working if I use as key . Query : Select JSON=[dbo].[udf-Str-JSON](0,1,(Select 1 as ID,* From ColourGroup for XML RAW))

John Cappelletti Over a year ago

@RameshRajendran Interesting. I never use AS but it should work if you Select id=1,* from ColourGroup fpr XML RAW

John Cappelletti Over a year ago

@RameshRajendran Using the sample data above AS seems to work Select JSON=[dbo].[svf-Str-JSON](0,1,(Select 1 as id,* From (at)Table For XML Raw))

Ramesh Rajendran Over a year ago

I already tried the both ways. everything is working. but only am getting error in these ways. I don't know why.

Alberto Silva Over a year ago

Simple suggestion that I've implemented myself, add an argument to force the results when @IncludeHead = 0 to be presented as a list (surrounded by square brackets) even if just one single result is returned

|

John Cappelletti · Accepted Answer · 2016-10-06 03:21:13Z

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','[email protected]','2016-10-05 17:32:41.903'),
(2,0,'Jane','Doe'  ,'[email protected]','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":"[email protected]", "LastOn":"2016-10-05T17:32:41.903", "TotalSales":25569.0000} ,{"ID":2, "Active":0, "First_Name":"Jane", "Last_Name":"Doe", "EMail":"[email protected]", "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.

Collectives™ on Stack Overflow

How to make JSON from SQL query in MS SQL 2014

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related