How to Make JSON from SQL Query in Ms SQL 2014

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.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":"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)
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

How can I generate JSON results in older SQL Server versions?

Something based on this?

DECLARE @DataSource TABLE
(
    [OrderID] INT
   ,[CustomerID] INT
   ,[ItemID] INT
   ,[ItemName] NVARCHAR(128)
);

INSERT INTO @DataSource ([OrderID], [CustomerID], [ItemID], [ItemName])
VALUES (1, 14, 6, 'Apples')
      ,(2, 14, 7, 'Oranges')
      ,(3, 23, 10, 'Mangoes');

WITH DataSource AS
(
    SELECT DISTINCT [CustomerID]
    FROM @DataSource
)
SELECT ROW_NUMBER() OVER (ORDER BY DS.[CustomerID]) AS ResultId      
      ,*
FROM DataSource DS
CROSS APPLY
(
    SELECT '[' + STUFF
    (
        (
            SELECT ',' + CONCAT('{"ItemId":', [ItemID],',"ItemName":"', [ItemName],'"}')
            FROM @DataSource
            WHERE [CustomerID] = DS.[CustomerID]
            ORDER BY [ItemID]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1
        ,1
        ,''
    ) + ']'
) Items ([OrderedItems]);

How to create JSON from variables in SQL SERVER using FOR JSON AUTO

SQL Server can't generate JSON with FOR JSON AUTO unless it has a schema to build the object from. The SELECT in your INSERT statement is just selecting a couple of variable values when trying to generate the JSON. In the scope of the cursor, SQL Server doesn't have any context in which to generate the JSON object(s).

You don't need a cursor for this. See if this will get you to what you want.

DECLARE @sourceTable TABLE (
    test1 int,
    test2 varchar(255)
);

DECLARE @destinationTable TABLE (testRow NVARCHAR(MAX));

INSERT @sourceTable  (test1, test2)
VALUES (5, 'something'),
       (6, 'something else');

WITH r AS (
    SELECT test1, test2, ROW_NUMBER() OVER (ORDER BY test1, test2) AS RowNum
    FROM @sourceTable 
)
INSERT @destinationTable (testRow)
SELECT (SELECT test1, test2 FROM r WHERE RowNum = r2.RowNum FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER)
FROM r r2;

SELECT testRow FROM @destinationTable;

The results from this will be:

{"test1":5,"test2":"something"}
{"test1":6,"test2":"something else"}

If you want each row to be in an array, remove WITHOUT_ARRAY_WRAPPER.

The CTE generates an identifier for each row and then the outer query generates the JSON for just that single row. This operates as a single set operation, though, so will greatly outperform a cursor solution as the source data grows. Any time you find yourself using a cursor to iterate through a table to select or update data you should take a step back and look for a set based method instead.

SQL to JSON - Create a Json Array

You can use STRING_AGG to build the array like this:

SELECT  DISTINCT ID
      ,Product
      ,json_query(QUOTENAME(STRING_AGG('"' + STRING_ESCAPE(Usage, 'json') + '"', char(44)))) AS 'Usage'
FROM mytable T1
GROUP BY ID
      ,Product
FOR JSON PATH;

If you are not using SQL Sever 2017 or later, you can use concatenate the values using XML PATH.

SELECT DISTINCT T1.ID
      ,T1.Product
      ,
       (
         '[' +
          STUFF
          (
                (
                SELECT ',' + '"' + T2.Usage + '"' 
                FROM mytable T2
                WHERE T1.ID=T2.ID
                FOR XML PATH, TYPE
                ).value('.', 'NVARCHAR(MAX)')
                ,1
                ,1
                ,''
            )
          + ']'
       ) as 'Usage'
FROM mytable T1
FOR JSON PATH

---

For your edit use:

SELECT Distinct ID
      ,Product
      ,json_query('[' + (STRING_AGG('"' + STRING_ESCAPE(TRIM(Usage), 'json') + '"', char(44))) + ']') AS 'Usage'
FROM mytable
GROUP BY ID
      ,Product
FOR JSON PATH;

The issue is QUOTENAME input is limited to 128 chars and returns NULL when you add more records.

Simple way to parse JSON in SQL 2014

Well, I guess then this is the simplest way to parse known JSON in SQL 2014 without external function ...

select @status = REPLACE(SUBSTRING(@json,charindex('status',@json)+8,(charindex('errlog',@json)-2)-(charindex('status',@json)+8)),'"','')
select @errlog = REPLACE(SUBSTRING(@json,charindex('errlog',@json)+8,(charindex('pl_number',@json)-2)-(charindex('errlog',@json)+8)),'"','')
select @parcelnum = REPLACE(SUBSTRING(@json,charindex('pl_number',@json)+12,charindex(']}',@json)-(charindex('pl_number',@json)+12)),'"','')

SQL Server SELECT to JSON function

Starting from SQL Server 2016 you can use for json:

declare @t table(id int, name nvarchar(max), active bit)
insert @t values (1, 'Bob Jones', 1), (2, 'John Smith', 0)

select id, name, active
from @t
for json auto

With older versions of SQL Server you can use for xml path, e.g.:

select '[' + STUFF((
        select 
            ',{"id":' + cast(id as varchar(max))
            + ',"name":"' + name + '"'
            + ',"active":' + cast(active as varchar(max))
            +'}'

        from @t t1
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

Output:

[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]

Convert select query results into Json

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,'')
    + ']';

Create JSON from SQL Server Table With Column Value as JSON Property Name

FOR JSON AUTO works from the column names, so one method to get your desired result would be to PIVOT the property names into columns. For example:

SELECT Color, [Name], Cost 
FROM dbo.Properties
PIVOT ( MAX( PropertyValue ) For PropertyName In ( [Color], [Name], Cost ) ) pvt
FOR JSON AUTO;

My results:

Of course this is only convenient if your JSON attributes / column names are always known and it's a simple example. For more complex examples, you are probably looking at dynamic pivot, or dynamic SQL and your STRING_AGG example isn't so bad.

---

Related Topics