Data Types

The Simba MongoDB ODBC Connector supports many MongoDB data types, and converts data between MongoDB and SQL data types as needed. The mapping between each data type is determined by the schema definition, which you can create by using the Schema Editor application that is installed with the connector.

Note:

The Schema Editor is not supported on AIX. On all other platforms, information about how to use the Schema Editor can be found in the Schema Editor User Guide located in the installation directory of the connector.

For information about how to use the Schema Editor, see the Schema Editor User Guide located in the installation directory of the connector.

On Windows 7 or earlier, the guide is available from the Simba MongoDB ODBC Driver program group in the Start menu.
On Windows 8 or later, you can search for the guide on the Start screen.

The following table lists the supported data type mappings.

To support complex data types such as objects and arrays, the connector renormalizes the data into virtual tables. For more information, see Virtual Tables.

MongoDB Type	SQL Type
Binary	SQL_BIGINT SQL_BIT SQL_DOUBLE SQL_INTEGER SQL_VARCHAR
Boolean	SQL_BIGINT SQL_BIT SQL_DOUBLE SQL_INTEGER SQL_VARCHAR
Date	SQL_TIMESTAMP
DBPointer	SQL_VARCHAR
Decimal	SQL_DECIMAL
JavaScript	SQL_VARCHAR
JavaScript (with scope)	SQL_VARCHAR Important: Important: MongoDB cannot represent JavaScript (with scope) data as a string. The connector converts the data to type SQL_VARCHAR, but returns the following string as the value: `Unsupported JavaScript with Scope`.
MaxKey	SQL_VARCHAR
MinKey	SQL_VARCHAR
NumberDouble	SQL_BIGINT SQL_DOUBLE SQL_INTEGER SQL_VARCHAR
NumberInt
NumberLong
ObjectID	SQL_VARCHAR
Regular Expression	SQL_VARCHAR
String	SQL_VARCHAR
Symbol	SQL_VARCHAR
Timestamp	SQL_VARCHAR
Undefined	SQL_VARCHAR
UUID	SQL_GUID
Array	N/A The data is renormalized into a virtual table.
Object	N/A The data is renormalized into a virtual table.

Different rows in the sampled data might have the same field assigned to different data types. The connector resolves this mixed data typing by specifying a single type for the field in the schema definition. The specified type is the first data type from this list that appears in the sampled data.

Array
Fields that have Object as one of the data types are also returned as Array data.
Binary
String
Fields that have any of the following types as one of the data types, but not Array or Binary, are also returned as String data:
- Date
- Timestamp
- DBPointer
- JavaScript
- JavaScript (with scope)
- Symbol
- Regular Expression
- MaxKey
- MinKey
- Undefined
- OID
NumberDouble
NumberLong
NumberInt

For example, the connector treats the following field named f as MongoDB data of type Array:

{f : {g1 : 1}}

{f : [1, 2, 3]}

Data types that do not have a direct mapping from MongoDB to ODBC are represented as type VARCHAR in ODBC. The detected MongoDB type is used during INSERT and UPDATE operations.

Note:

The default precision for SQL_TYPE_TIMESTAMP is 3.

The MongoDB-type ISODate that is mapped to SQL_TYPE_TIMESTAMP stores the timestamp value in the number of milliseconds since the Unix epoch (January 1, 1970). As the value is in milliseconds, the precision cannot be greater than 3.

To revert to the previous SQL_TYPE_TIMESTAMP behavior (precision is set to 6, and precision in schema maps is ignored), append the connection string as follows:

IgnoreTimestampPrecisionFromSchemaMap=1;DefaultTimestampPrecision=6;

Be aware that if you revert to the previous behavior, the connector does not warn about the timestamp truncation.

Embedded Documents

The connector renormalizes embedded documents into columns. For example, consider the following JSON document:

{"contact": {"address": {"street": "1-123 Broadway", "city": "Vancouver"}}, "phone": "+12345678"}

When generating the schema definition, the connector identifies the following columns in the document, all of type String:

contact_address_street	contact_address_city	phone
1-123 Broadway	Vancouver	+12345678

The connector is able to work with these columns as if they were standard table columns.

Arrays

The ODBC interface does not natively support collection-based data types, so the Simba MongoDB ODBC Connector implements two options for accessing and interacting with collection-based data. Depending on preference, arrays in MongoDB can be renormalized into virtual tables or columns.

By default, arrays are renormalized into virtual tables. To view array elements as columns instead, use the Move to Parent option in the Schema Editor. You can create the column in a parent virtual table or in the base table.

Note:

For information about how to use the Schema Editor, see the Schema Editor User Guide located in the installation directory of the connector.

On Windows 7 or earlier, the guide is available from the SimbaMongoDB ODBC Driver program group in the Start menu.
On Windows 8 or later, you can search for the guide on the Start screen.

Arrays as Virtual Tables

The connector can renormalize MongoDB arrays into virtual tables. For more information, see Virtual Tables.

Arrays as Columns

The connector can also renormalize MongoDB arrays into columns. Consider the following JSON document:

{"values": ["hello", 1, {"v1": {"v2": "this is an embedded document"}}]}

The connector can represent the array elements using the following columns, where values_1 is of type Double and the other two are of type String:

values_0	values_1	values_2_v1_v2
Hello	1.0	this is an embedded document

The connector works with these columns as if they were standard table columns.

The column names include the index of the array element that the column represents, starting with an index of 0. In other words, the first element of the array uses a suffix of _0, the second element uses _1, and so on.