Schema Mapping

Auto-detect fields, map BSON types to SQL, and customize every column.

Auto schema inference

When you create a migration, NoSQLSync samples up to 500 documents from your MongoDB collection and infers the schema. Each field is assigned a MongoDB type based on observed values. The resolver maps each type to the best SQL equivalent for your target dialect.

The introspection engine walks every nested field path and records: observed types, null fraction, and sample values. This powers the visual Field Mapping Table in the Schema Mapper.

BSON → PostgreSQL type mapping

MongoDB TypePostgreSQL TypeNotes
ObjectIdUUID12-byte identifier → standard UUID
StringTEXT / VARCHAR(n)Configurable max length
NumberIntINTEGER32-bit signed integer
NumberDoubleDOUBLE PRECISION64-bit floating point
ISODateTIMESTAMPTZFull timezone-aware timestamp
BooleanBOOLEANStandard true/false
ArrayJSONB / ARRAYTyped arrays or generic JSONB
Nested ObjectJSONBIndexed with dot-notation queries
BinaryBYTEAVariable-length binary data

BSON → BigQuery type mapping

MongoDB TypeBigQuery TypeNotes
ObjectIdSTRINGHex-encoded string
StringSTRINGUnlimited Unicode
NumberIntINT6464-bit integer
NumberDoubleFLOAT64IEEE 754 double
ISODateTIMESTAMPUTC microsecond precision
BooleanBOOLNative boolean
ArrayARRAY<T> / JSONTyped or flexible arrays
Nested ObjectSTRUCT / JSONStructured or flexible
BinaryBYTESBase64-encoded binary

Type mapping for all targets

NoSQLSync supports 8 SQL dialects with dialect-specific type mappings. The type mapper resolves every MongoDB type to the best native SQL type for your target:

  • PostgreSQL: UUID, TEXT, INTEGER, TIMESTAMPTZ, JSONB, BYTEA
  • MySQL: BIGINT UNSIGNED, VARCHAR, INT, DATETIME(3), JSON, BLOB
  • BigQuery: STRING, INT64, FLOAT64, TIMESTAMP, STRUCT
  • Snowflake: VARCHAR, NUMBER, TIMESTAMP_NTZ, VARIANT, OBJECT, ARRAY
  • Redshift: VARCHAR, BIGINT, DOUBLE PRECISION, TIMESTAMP, SUPER
  • ClickHouse: String, Int32, Float64, DateTime64, JSON
  • SQL Server: VARCHAR, BIGINT, FLOAT, DATETIME2, NVARCHAR(MAX)
  • MariaDB: VARCHAR, INT, DOUBLE, DATETIME(3), LONGTEXT

Handling nested documents and arrays

MongoDB nested documents become JSONB (or equivalent) by default. The Schema Mapper offers three strategies:

  • JSONB: Keep nested structure intact — queryable with native JSON operators
  • Flattened: Extract specific fields from nested docs into top-level columns
  • Normalized: Create separate tables with foreign keys for one-to-many arrays

Example: { user: { name: "Alex", email: "[email protected]" } } becomes user_name TEXT, user_email TEXT (flattened) or user JSONB.

Column renaming and aliasing

In the Field Mapping Table, click any Target Column to rename it. MongoDB field names like created_at.$date are automatically sanitized to safe SQL names like created_at_date. You can override any auto-generated name.

Custom type overrides

Click the SQL Type column to select a different type from the dropdown. Mark fields as NOT NULL or primary key. The _id field from MongoDB is automatically set as the primary key. You can also export the schema as DDL (CREATE TABLE) for review.