JSON In SQL Server

Facebooktwitterredditpinterestlinkedinmail

Finally in SQL Server 2016, Microsoft SQL Server now supports JSON serialization. It provides it through the FOR clause… like XML serialization. This will take a recordset and output it in JSON format with very little effort by the coder.

SELECT	AnimalID,
AnimalName,
Color
FROM	Animal
FOR	JSON PATH

As you can see. To get the output to come in JSON format, all you need to do is add FOR JSON PATH at the end of your query. Simple right? Here is what the results would look like.

[
	{
		"AnimalID":1,
		"AnimalName":"Dog",
		"Color":"Brown"
	},
	{
		"AnimalID":2,
		"AnimalName":"Flamingo",
		"Color":"Pink"
	},
	{
		"AnimalID":3,
		"AnimalName":"Polar Bear",
		"Color":"White"
	}
]

1 thought on “JSON In SQL Server”

  1. I was wondering if it was the same for XML and sure enough it is, just replace “JSON” with “XML”:

    SELECT AnimalID,
    AnimalName,
    Color
    FROM Animal
    FOR XML PATH

    This works in SQL Server 2008 too (not sure about earlier versions).

    Reply

Leave a Comment

CommentLuv badge