Converting SQL query result into JSON string

SQL doesn't provide native support to translate query result into JSON format. But we can achive this with a little twisting with SQL :).

Assume we have a "Computer" table in database with the following data:

Now, we can get this data into JSON format using the query below:

	DECLARE @JsonString NVARCHAR(4000) = '[', @ProductCode NVARCHAR(100) = ''
	WHILE @ProductCode IS NOT NULL
	BEGIN
	SELECT @ProductCode = MIN(ProductCode)
	FROM Computer
	WHERE ProductCode > @ProductCode
	IF @ProductCode IS NOT NULL
	BEGIN
	SET @JsonString = @JsonString + CASE @JsonString WHEN '[' THEN '{' ELSE ',{' END
	SET @JsonString = @JsonString + '"ProductCode":"' + @ProductCode + '"'
	SELECT @JsonString = @JsonString + ',"' + Attribute + '":' + '"' + Value + '"'
	FROM Computer
	UNPIVOT
	(
	Value
	FOR Attribute IN (Category, Brand, Model, Color) -- columns to be serialized
	) u
	WHERE ProductCode = @ProductCode
	SET @JsonString = @JsonString + '}'
	END
	END
	SELECT @JsonString + ']' AS JsonString

view raw convert-to-json.sql hosted with ❤ by GitHub

Here, I've used UNPIVOT operation to de-normalized the data and then constructed the JSON string. Notice that: the ProductCode has been used as the primary key of the objects in this query.

You can also achive the same thing without using UNPIVOT; but in that case you have to construct the JSON string for every column you need by yourself.

And if you are looking for more generic one to convert each row from a data set regardless of any primary key or without knowing the exact columns to be converted, following stored procedure could be helpful:

	/******************************************************************************
	-- This procedure is written to convert any query result to json string.
	-- It fails if there is any column that is not convertible to nvarchar (e.g. BinaryData).
	-- And also it doesn't handle invalid characters (e.g. escape characters).
	-- Copyright © 2016 Samina Azad. All rights reserved.
	-- Web: http://saminaazad.blogspot.com
	-- Date modified: 27th March 2016
	******************************************************************************/
	-- exec [ConvertToJson] N'select * from Computer'
	CREATE PROCEDURE [dbo].[ConvertToJson]
	@rawQuery NVARCHAR(2000)
	AS
	BEGIN
	DECLARE @internalQuery NVARCHAR(MAX) = N'SELECT IDENTITY (INT,1,1) AS RowId, * INTO ##tmpRecords FROM (' + @rawQuery + ') data'
	EXEC sp_executesql @internalQuery
	DECLARE @ColumnNames NVARCHAR(MAX) = '', @SelectClause nvarchar(max) = ''
	SELECT @ColumnNames = @ColumnNames + CASE @ColumnNames WHEN '' THEN QUOTENAME(name) ELSE ',' + QUOTENAME(name) END,
	@SelectClause = @SelectClause + CASE @SelectClause WHEN '' THEN 'CAST(' + QUOTENAME(name) + ' AS NVARCHAR(2000)) ' + name ELSE ', CAST(' + QUOTENAME(name) + ' AS NVARCHAR(2000)) ' + name END
	FROM tempdb.sys.columns
	WHERE OBJECT_ID = OBJECT_ID('tempdb..##tmpRecords') AND column_id > 1
	SET @SelectClause = 'SELECT RowId, ' + @SelectClause + ' FROM ##tmpRecords'
	DECLARE @normalizedQuery NVARCHAR(MAX) = N'SELECT RowId, data.Attribute, data.Value
	INTO ##tmpNormalized
	FROM
	(' + @SelectClause + ') d
	UNPIVOT
	(
	Value
	FOR Attribute IN (' + @ColumnNames + ')
	) data'
	EXEC sp_executesql @normalizedQuery
	DECLARE @JsonString NVARCHAR(4000) = '[', @RowId NVARCHAR(100) = ''
	WHILE @RowId is not null
	BEGIN
	SELECT @RowId = MIN(RowId)
	FROM ##tmpNormalized
	WHERE RowId > @RowId
	IF @RowId IS NOT NULL
	BEGIN
	SET @JsonString = @JsonString + CASE @JsonString WHEN '[' THEN '{' ELSE ',{' END
	SELECT @JsonString = @JsonString + CASE WHEN SUBSTRING(@JsonString, LEN(@JsonString), 1) = '{' THEN '"' ELSE ',"' END + Attribute + '":' + '"' + Value + '"'
	FROM ##tmpNormalized
	WHERE RowId = @RowId
	SET @JsonString = @JsonString + '}'
	END
	END
	SELECT @JsonString + ']' AS JsonString
	DROP TABLE ##tmpRecords
	DROP TABLE ##tmpNormalized
	END

view raw ConvertToJson.sql hosted with ❤ by GitHub

I hope this will be helpful to someone looking for generating JSON feed directly from SQL. Cheers!