How FOR JSON converts SQL Server data types to JSON data types (SQL Server)

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (serverless SQL pool only)

The FOR JSON clause uses the following rules to convert SQL Server data types to JSON types in the JSON output.

Category	SQL Server data type	JSON data type
Character & string types	char, nchar, varchar, nvarchar	string
Numeric types	int, bigint, float, decimal, numeric	number
Bit type	bit	Boolean (true or false)
Date & time types	date, datetime, datetime2, time, datetimeoffset	string
Binary types	varbinary, binary, image, timestamp, rowversion	BASE64-encoded string
CLR types	geometry, geography, other CLR types	Not supported. These types return an error. In the SELECT statement, use CAST or CONVERT, or use a CLR property or method, to convert the source data to a SQL Server data type that can be converted successfully to a JSON type. For example, use STAsText() for the geometry type, or use ToString() for any CLR type. The type of the JSON output value is then derived from the return type of the conversion that you apply in the SELECT statement.
Other types	uniqueidentifier, money	string

Learn more about JSON in SQL Server and Azure SQL Database

Microsoft videos

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

• JSON as a bridge between NoSQL and relational worlds

See Also

Format Query Results as JSON with FOR JSON (SQL Server)