Data API Data Type Conversion

On this page

SingleStore’s Data API returns JSON objects as responses. The following tables map the SingleStore data types to the corresponding JSON data types.

Data Type Mapping for `/api/v2/`

Category	SingleStore Data Type	JSON Data Type	Notes
Boolean	BOOL TINYINT	Number	A `TINYINT` value of `0` is considered `false`, and non-zero values are considered `true`. SingleStore returns `false` as `0` and `true` as `1`.
Numeric	TINYINT SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE	Number	JSON number data type supports both integers and floating point values, and they are returned as-is from SingleStore. For example, 24 25567 3.14 2345.45632 1e20 1.34e-10 The API supports 64-bit unsigned integer data type.
JSON	JSON	JSON	JSON type values are directly added to the row tuple without any type conversion, which eliminates the need for additional parsing in later steps. For example, { "hello": "world" } [ 1, 2, null, 3 ] "data" 1.4 JSON null values and SQL null values cannot be differentiated when using the Data API.
String and Geospatial	DECIMAL GEOGRAPHY GEOGRAPHYPOINT TEXT CHAR VARCHAR TINYTEXT MEDIUMTEXT	String	JSON strings are enclosed within double quotes. For example, "567.2389354" "POINT(-74.04451396 40.68924403)" "POLYGON((1 1,2 1,2 2, 1 2, 1 1))" "Connectors"
Varbinary	VARBINARY LONGBLOB MEDIUMBLOB TINYBLOB BLOB	Base64-encoded string	SingleStore `VARBINARY` values are converted to base-64 encoded strings as JSON values. Hence, you may need additional parsing before using these values. The following examples show SingleStore values and their equivalent JSON strings: A `VARBINARY(200)` type value of 'databases' is converted to "ZGF0YWJhc2Vz". A `TINYBLOB` type value of 'binary' is converted to "YmluYXJ5" A `BLOB` type value of 'blobs' is converted to "YmxvYnM" To return binary column values as string, you can cast it. For example, `SELECT CAST(binary_col_name AS CHAR)...`
Time and Date	YEAR TIME DATE DATETIME TIMESTAMP DATETIME(6) TIMESTAMP(6)	String	Date and time values are converted to JSON strings, and they are no longer parsed through the Golang time module. For example, "2022" "12:04:00" "2006-01-02" "2006-01-02 15:04:05" "2006-01-02 15:04:59.735214" `DATETIME`/`TIMESTAMP` values with `0` microseconds precision follow SingleStore semantics.

Important

If a column is nullable, make sure to prepare to handle null values.

Data Type Mapping for `/api/v1/`

Category	SingleStore Data Type	JSON Data Type	Notes
Boolean	BOOL TINYINT	Number	A `TINYINT` value of `0` is considered `false`, and non-zero values are considered `true`. SingleStore returns `false` as `0` and `true` as `1`.
Numeric	TINYINT SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE	Number	JSON number data type supports both integers and floating point values. For example, 24 25567 3.14 2345.45632 1e20 1.34e-10
JSON	JSON	JSON	JSON type values are directly added to the row tuple without any type conversion, which eliminates the need for additional parsing in later steps. For example, { "hello": "world" } [ 1, 2, null, 3 ] "data" 1.4 JSON null values and SQL null values cannot be differentiated when using the Data API.
String and Geospatial	DECIMAL GEOGRAPHY GEOGRAPHYPOINT TEXT CHAR VARCHAR TINYTEXT MEDIUMTEXT	String	JSON strings are enclosed within double quotes. For example, "567.2389354" "POINT(-74.04451396 40.68924403)" "POLYGON((1 1,2 1,2 2, 1 2, 1 1))" "Connectors"
Varbinary	VARBINARY LONGBLOB MEDIUMBLOB TINYBLOB BLOB	Base64-encoded string	SingleStore `VARBINARY` values are converted to base-64 encoded strings as JSON values. Hence, you may need additional parsing before using these values. The following examples show SingleStore values and their equivalent JSON strings: A `VARBINARY(200)` type value of 'databases' is converted to "ZGF0YWJhc2Vz". A `TINYBLOB` type value of 'binary' is converted to "YmluYXJ5" A `BLOB` type value of 'blobs' is converted to "YmxvYnM" To return binary column values as string, you can cast it. For example, `SELECT CAST(binary_col_name AS CHAR)...`
Time and Date	YEAR	Number	`YEAR` type values are converted to JSON number type. For example, 2001.
	TIME DATE DATETIME TIMESTAMP DATETIME(6) TIMESTAMP(6)	String	Date and time values (except for `YEAR`) are converted to JSON strings. For example, "12:04:00" "2006-01-02" "2006-01-02 15:04:05" "2006-01-02 15:04:59.735214"

Important

If a column is nullable, make sure to prepare to handle null values.
Invalid DATETIME and TIMESTAMP values cannot be used over the Data API, although SingleStore allows them without the HTTP proxy.
UNSIGNED numbers must be specified as NOT NULL. The Data API returns an error if it receives a value larger than the maximum SIGNED value and if the result type is NOT NULL.

Prerequisite to Upgrade from Data API v1 to v2

To upgrade from Data API v1 to v2:

Change /api/v1/ in the API request URL to /api/v2/.
Note the data type conversion differences between /api/v1 and /api/v2. For example, the YEAR data type is converted to a JSON string in /api/v2 and a JSON number in /api/v1.

Last modified: June 10, 2022

Was this article helpful?