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

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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:

See Also

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