Data Types

The Simba MongoDB JDBC Driver supports all MongoDB data types, and converts data between MongoDB, SQL, and Java 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:

For information about how to use the Schema Editor, see the Schema Editor User Guide for JDBC Connections.

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 Java Type

Binary

SQL_BINARY

Binary

Boolean

SQL_BIGINT

SQL_BIT

SQL_DOUBLE

SQL_INTEGER

SQL_VARCHAR

Boolean

Double

Int

Long

Varchar

Date

SQL_TIMESTAMP

Timestamp
Decimal SQL_DECIMAL BigDecimal

NumberDouble

SQL_BIGINT

SQL_DOUBLE

SQL_INTEGER

SQL_VARCHAR

Double

Int

Long

Varchar

NumberInt

SQL_BIGINT

SQL_DOUBLE

SQL_INTEGER

SQL_VARCHAR

Double

Int

Long

Varchar

NumberLong

SQL_BIGINT

SQL_DOUBLE

SQL_INTEGER

SQL_VARCHAR

Double

Int

Long

Varchar

ObjectID

SQL_VARCHAR

Varchar

String

SQL_WVARCHAR

Varchar

UUID

SQL_BINARY

Binary

Array

N/A

The data is renormalized into a virtual table.

N/A

The data is renormalized into a virtual table.

Object

N/A

The data is renormalized into columns.

N/A

The data is renormalized into columns.

If different rows in the sampled data have the same field assigned to different data types, then the connector resolves this mixed data typing by specifying a single type for the field in the schema definition, based on the following precedence order. The connector chooses the first data type from this list that appears in the sampled data.

  1. Array

    Fields that have Object has one of the data types are also returned as Array data.

  2. Binary
  3. String

    Fields that have any of the following types as one of the data types are also returned as String data:

    • Date
    • Timestamp
    • DBPointer
    • JavaScript
    • JavaScript (with scope)
    • Symbol
    • Regular Expression
    • MaxKey
    • MinKey
    • Undefined
    • OID
  4. NumberDouble
  5. NumberLong
  6. 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.

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 JDBC interface does not natively support collection-based data types, so the Simba MongoDB JDBC Driver 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 more information about using the Schema Editor to manage your schema definition, see the Schema Editor User Guide for JDBC Connections.

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.

Related topics