Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make JSON from SQL query in MS SQL 2014

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
like image 410
Joachim Langezaal Avatar asked Oct 05 '16 20:10

Joachim Langezaal


People also ask

Is JSON available in SQL Server 2014?

Microsoft introduced built-in JSON functions in SQL Server to align with the web services that use JSON for their data transmission.

Can SQL query JSON?

You don't need a custom query language to query JSON in SQL Server. To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function.

Does MS SQL support JSON?

SQL Server and Azure SQL Database have native JSON functions that enable you to parse JSON documents using standard SQL language. You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database.


1 Answers

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

enter image description here

cteEAV will dynamically unpivot the data and generate the following:

enter image description here

cteBLD will extend and add flags New/End Row enter image description here

The Final Select

This will put it all together and generate one final string which can be wrapped or nested as you please.

like image 107
John Cappelletti Avatar answered Oct 19 '22 12:10

John Cappelletti