# Data API Data Type Conversion

SingleStore’s Data API returns JSON objects as responses. The following tables map the [SingleStore data types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) to the corresponding JSON data types.

## Data Type Mapping for `/api/v2/`

| Category              | SingleStore Data Type                                                                                                                                     | JSON Data Type        | Notes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| --------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Boolean               | <ul> <li>BOOL</li> <li>TINYINT</li> </ul>                                                                                                                 | 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               | <ul> <li>TINYINT</li> <li>SMALLINT</li> <li>MEDIUMINT</li> <li>INT</li> <li>BIGINT</li> <li>FLOAT</li> <li>DOUBLE</li> </ul>                              | Number                | JSON number data type supports both integers and floating point values, and they are returned as-is from SingleStore. For example,<ul> <li>24</li> <li>25567</li> <li>3.14</li> <li>2345.45632</li> <li>1e20</li> <li>1.34e-10</li> </ul>The API supports 64-bit unsigned integer data type.                                                                                                                                                                                                                                                                                                                                         |
| JSON                  | <ul> <li>JSON</li> </ul>                                                                                                                                  | 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,<ul> <li>{ "hello": "world" }</li> <li>[ 1, 2, null, 3 ]</li> <li>"data"</li> <li>1.4</li> </ul>JSON null values and SQL null values cannot be differentiated when using the Data API.                                                                                                                                                                                                                                                                                |
| String and Geospatial | <ul> <li>DECIMAL</li> <li>GEOGRAPHY</li> <li>GEOGRAPHYPOINT</li> <li>TEXT</li> <li>CHAR</li> <li>VARCHAR</li> <li>TINYTEXT</li> <li>MEDIUMTEXT</li> </ul> | String                | JSON strings are enclosed within double quotes. For example,<ul> <li>"567.2389354"</li> <li>"POINT(-74.04451396 40.68924403)"</li> <li>"POLYGON((1 1,2 1,2 2, 1 2, 1 1))"</li> <li>"Connectors"</li> </ul>                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Varbinary             | <ul> <li>VARBINARY</li> <li>LONGBLOB</li> <li>MEDIUMBLOB</li> <li>TINYBLOB</li> <li>BLOB</li> </ul>                                                       | 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:<ul> <li>A <code>VARBINARY(200)</code> type value of 'databases' is converted to "ZGF0YWJhc2Vz".</li> <li>A <code>TINYBLOB</code> type value of 'binary' is converted to "YmluYXJ5"</li> <li>A <code>BLOB</code> type value of 'blobs' is converted to "YmxvYnM"</li> </ul>To return binary column values as string, you can cast it. For example,`SELECT CAST(binary_col_name AS CHAR)...` |
| Time and Date         | <ul> <li>YEAR</li> <li>TIME</li> <li>DATE</li> <li>DATETIME</li> <li>TIMESTAMP</li> <li>DATETIME(6)</li> <li>TIMESTAMP(6)</li> </ul>                      | String                | Date and time values are converted to JSON strings, and they are no longer parsed through the Golang time module. For example,<ul> <li>"2022"</li> <li>"12:04:00"</li> <li>"2006-01-02"</li> <li>"2006-01-02 15:04:05"</li> <li>"2006-01-02 15:04:59.735214"</li> </ul>`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                                                                                                                | <ul> <li>BOOL</li> <li>TINYINT</li> </ul>                                                                                                                 | 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                                                                                                                | <ul> <li>TINYINT</li> <li>SMALLINT</li> <li>MEDIUMINT</li> <li>INT</li> <li>BIGINT</li> <li>FLOAT</li> <li>DOUBLE</li> </ul>                              | Number                                                                                                                                                                                                       | JSON number data type supports both integers and floating point values. For example,<ul> <li>24</li> <li>25567</li> <li>3.14</li> <li>2345.45632</li> <li>1e20</li> <li>1.34e-10</li> </ul>                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| JSON                                                                                                                   | <ul> <li>JSON</li> </ul>                                                                                                                                  | 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,<ul> <li>{ "hello": "world" }</li> <li>[ 1, 2, null, 3 ]</li> <li>"data"</li> <li>1.4</li> </ul>JSON null values and SQL null values cannot be differentiated when using the Data API.                                                                                                                                                                                                                                                                                |
| String and Geospatial                                                                                                  | <ul> <li>DECIMAL</li> <li>GEOGRAPHY</li> <li>GEOGRAPHYPOINT</li> <li>TEXT</li> <li>CHAR</li> <li>VARCHAR</li> <li>TINYTEXT</li> <li>MEDIUMTEXT</li> </ul> | String                                                                                                                                                                                                       | JSON strings are enclosed within double quotes. For example,<ul> <li>"567.2389354"</li> <li>"POINT(-74.04451396 40.68924403)"</li> <li>"POLYGON((1 1,2 1,2 2, 1 2, 1 1))"</li> <li>"Connectors"</li> </ul>                                                                                                                                                                                                                                                                                                                                                                                                                           |
| Varbinary                                                                                                              | <ul> <li>VARBINARY</li> <li>LONGBLOB</li> <li>MEDIUMBLOB</li> <li>TINYBLOB</li> <li>BLOB</li> </ul>                                                       | 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:<ul> <li>A <code>VARBINARY(200)</code> type value of 'databases' is converted to "ZGF0YWJhc2Vz".</li> <li>A <code>TINYBLOB</code> type value of 'binary' is converted to "YmluYXJ5"</li> <li>A <code>BLOB</code> type value of 'blobs' is converted to "YmxvYnM"</li> </ul>To return binary column values as string, you can cast it. For example,`SELECT CAST(binary_col_name AS CHAR)...` |
| Time and Date                                                                                                          | <ul> <li>YEAR</li> </ul>                                                                                                                                  | Number                                                                                                                                                                                                       | `YEAR`type values are converted to JSON number type. For example, 2001.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| <ul> <li>TIME</li> <li>DATE</li> <li>DATETIME</li> <li>TIMESTAMP</li> <li>DATETIME(6)</li> <li>TIMESTAMP(6)</li> </ul> | String                                                                                                                                                    | Date and time values (except for`YEAR`) are converted to JSON strings. For example,<ul> <li>"12:04:00"</li> <li>"2006-01-02"</li> <li>"2006-01-02 15:04:05"</li> <li>"2006-01-02 15:04:59.735214"</li> </ul> |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |

> **❗ 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`.

***

Modified at: June 10, 2022

Source: [/db/v9.1/reference/data-api/data-api-data-type-conversion/](https://docs.singlestore.com/db/v9.1/reference/data-api/data-api-data-type-conversion/)

(An index of the documentation is available at /llms.txt)