MongoDB collections have no fixed schema, so dotConnect for MongoDB determines the schema of a collection from its documents and from its $jsonSchema validator, if one is defined. This topic describes how the provider maps MongoDB objects to relational ones and how it determines the columns exposed for a collection.

Mapping of MongoDB Objects

• MongoDB databases are represented as catalogs.
• MongoDB collections are represented as tables.
• MongoDB views are represented as views.
• Document fields are represented as table columns.
• Fields of embedded documents and elements of arrays are represented as columns with dotted names, for example address.city and tags.0.
• The _id field is mapped to the primary key, and the indexes of a collection are the indexes of its table. MongoDB has no foreign keys.

How the Columns Are Determined

The provider reads up to Describe Amount documents of a collection (100 by default) and determines the columns from the fields found in them. The following rules apply to the data types of the columns:

• If a field has the Int32 type in some documents and the Int64 type in others, the column has the Int64 type.
• If a field has the Int32 type in some documents and the Double type in others, the column has the Double type.
• If a field has null values and one non-null type, the field takes that non-null type. If all sampled values are null, the field has the String type.
• Int64 with Double gives the String type, because a Double cannot hold every Int64 value.
• If none of the preceding rules apply, the field has the String type.

The type rules are commutative: the resulting type does not depend on the order in which the documents are processed. For example, a field with Int32, Int64, and Double values gets the String type regardless of the order.

Fields can also be defined in a collection's $jsonSchema validator. These fields are columns even if no sampled document contains them; for an empty collection, they are all the columns. The data types of these fields are taken from the validator; if the sampled documents also contain the field, the type from the documents takes precedence. In SQL mode, a field whose MongoDB name contains a literal dot (not a nested-document path like address.city) is not exposed as a column, and fields that differ in case only are suffixed, for example Name and name_1.

A field that occurs only in documents beyond the sampled Describe Amount documents does not become a column. Fields specified in the Json Fields connection string parameter are included regardless of whether they appear in the sampled documents.

A document outside the sample is still returned, even if one of its fields has a type different from the type of the column.

Why Are Some Fields Missing from the Schema?

If a MongoDB field does not appear in the column metadata, it may exist only in documents outside the sampled set. To include it, increase Describe Amount or add the field to the Json Fields connection string parameter. Fields defined in a collection's $jsonSchema validator are always included in the metadata.

Caching

dotConnect for MongoDB does not store metadata between connections, so there is no metadata cache to refresh and no RefreshMetadata method. In SQL mode, a collection is described the first time a query of the connection uses it, and its columns stay the same while the connection is open. A new connection sees the fields added since. The GetSchema method reads the metadata from the server on each call.

Getting Metadata

There are two ways to access metadata with dotConnect for MongoDB: using the GetSchema method of MongoDBConnection or querying the information schema tables (SYS_TABLES, SYS_COLUMNS and others). The information schema tables are available in SQL mode and describe the database of the connection. GetSchema works in SQL and in native mode and can list every database of the server. For details, see the Using GetSchema Method and Information Schema topics.

See Also

Metadata | Using GetSchema Method | Information Schema