In SQL mode, dotConnect for MongoDB represents MongoDB collections as tables and document fields as columns. This topic describes how collections and documents are mapped to tables and columns.

Collections

• A collection of the database specified in the Database connection string parameter is a table of the main schema. To refer to a collection of another database, use the [otherdb].collection syntax.
• If a collection name contains dots, enclose it in square brackets, for example: [orders.archive].
• A table is described when a query refers to it for the first time. Thus, collections created after the connection is opened are also available.
• To create a collection, use the CREATE TABLE statement or the insert command in native mode. An INSERT statement that targets a nonexistent collection fails with the "no such table" error unless the Create Collection On Insert connection string parameter is set to True (it's False by default).
• The sqlite_master table and PRAGMA statements return information only about the tables that have already been used in queries.

Columns

dotConnect for MongoDB determines the columns of a table and their types by analyzing the first documents of the collection. The number of documents to analyze is set by the Describe Amount connection string parameter (100 by default).

If a field has values of different types in the analyzed documents, the column type is determined in the following way:

• Int32 and Int64 values result in an Int64 column.
• Int32 and Double values result in a Double column.
• Null values take the type of the other values.
• Any other combination of types results in a String column.

A table can have up to 2000 columns.

Nested Fields

Fields of embedded documents and array elements are represented as columns with dotted names, for example, address.city or tags.0.

In SQL statements, enclose such column names in double quotes or square brackets, for example, "address.city" or [address.city]. Otherwise, the statement fails with the "no such column: address.city" error.

Special BSON Types

The following table lists the special BSON types that are split into several columns.

In this table, re, bin, ts, and code are field names.

Embedded Documents and Arrays as JSON

You can represent embedded documents and arrays as JSON columns instead of dotted columns:

• If the Complex As String connection string parameter is set to True, each embedded document or array is represented as a single JSON column.
• The Json Fields connection string parameter specifies the fields to represent as JSON columns, for example: Json Fields=customers.address;customers.tags. A listed field is represented as a column even if no document contains it.

When you write data to such a column, a text value that starts with { or [ is parsed as Extended JSON.

The fields of such a JSON document are also available as hidden columns. Hidden columns aren't returned by SELECT *, but you can use them in queries. For example, with Json Fields=customers.address, the following query returns the address column as {"city":"London","street":"Baker St"}:

SELECT name, address FROM customers WHERE "address.city" = 'London'

To get a value from a JSON column, use the MONGO_JSON_VALUE(json, path) SQL function. For example, the following expression returns Paris for a document with "city": "Paris" in the address field:

MONGO_JSON_VALUE(address, '$.city')