As mentioned earlier, JSON does not have the same data types as SQL Server. Therefore, when JSON text is generated from relational data, a data type conversion is performed. The FOR JSON clause uses the following mapping to convert SQL Server data types to JSON types in the JSON output:
Conversion between SQL Server and JSON data types
|
SQL Server data type |
JSON data type |
|
Char, Varchar, Nchar, NVarchar, Text, Ntext, Date, DateTime, DateTime2, DateTimeOffset, Time, UniqueIdentifier, Smallmoney, Money, XML, HierarchyId, Sql_Variant |
string |
|
Tinyint, Smallint, Int, Bigint, Decimal, Float, Numeric |
number |
|
Bit |
true or false |
|
Binary, Varbinary, Image, Rowversion, Timestamp |
encoded string (BASE 64) |
The following data types are not supported: geography, geometry, and CLR-based user-defined data types. Thus, you cannot generate JSON output from tabular data if it includes columns of the aforementioned data types. For instance, the following query will fail:
SELECT * FROM Application.Cities FOR JSON AUTO;
Instead of returning a JSON output, it will generate an error with the following error message:
Msg 13604, Level 16, State 1, Line 282 FOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries.
The reason for the error is the Location column in the Cities table. Its data type is geography.
User-defined data types (UDT) are supported and will be converted following the same rules as underlined data types.