json

SQL Server Data Types To JSON Data Types

by
Facebooktwitterredditpinterestlinkedinmail

In SQL Server 2016 they are added support for outputting your query results in JSON. When you serialize your data to JSON using the FOR JSON clause, a data type conversion happens. The data types are converted from SQL Server data types to JSON supported data types.

For more information on how to convert your result set to JSON: www.sqlserver.info/syntax/json-in-sql-server/

Here is a quick reference table showing the data type conversions that are happening:

SQL Data Type JSON Data Type
VARCHAR/NVARCHAR String
CHAR/NCHAR String
INT Number
BIGINT Number
FLOAT Number
DECIMAL Number
NUMERIC Number
BIT Boolean
DATETIME String
DATETIME2 String
DATE String
TIME String
DATETIMEOFFSET String
VARBINARY Base64-encoded string
BINARY Base64-encoded string
IMAGE Base64-encoded string
TIMESTAMP Base64-encoded string
ROWVERSION Base64-encoded string
UNIQUEIIDENTIFIER String
MONEY String

 

JSON In SQL Server

by
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.

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.