Skip to main content

SingleStoreDB v7.8

Data API Data Type Conversion

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.

In this section: