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?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

(Optional) Run the following command to view the associated signature files.
Shell
```
curl undefined
```
Download the signature file from the SingleStore release server.
- Option 1: Click the Download Signature button next to the SingleStore file.
- Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.
- Option 3: Run the following command to download the signature file.
  Shell
```
curl -O undefined
```

After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

Shell

echo -n undefined |
    cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
      --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
      --bundle undefined \
      --new-bundle-format -

Verified OK