duckdb-mongo

Integrates DuckDB with MongoDB, enabling direct SQL queries and writes over MongoDB collections without ETL.

Announcement

We currently support DuckDB v2.0.0. The extension is built against the DuckDB submodule in this repo, and that submodule is updated when new DuckDB releases are validated. See How to Maintain an Extension Through DuckDB Releases for the community extension policy.

Note: Community extensions are built and distributed only for the latest stable DuckDB release. Older DuckDB versions can keep using the last compatible mongo extension build, but they will not receive updates. To keep getting new mongo extension releases, upgrade DuckDB to the latest stable version. To confirm the version locally, run SELECT version(); in DuckDB (or duckdb --version if you are using the CLI).

Quick Start

-- Attach to MongoDB
ATTACH 'host=localhost port=27017' AS mongo_db (TYPE MONGO);

-- Query your collections
SELECT * FROM mongo_db.mydb.mycollection LIMIT 10;

-- Load a CSV directly into MongoDB
INSERT INTO mongo_db.mydb.mycollection
SELECT * FROM read_csv('data.csv');

Using Secrets with MongoDB Atlas (recommended for production):

See the DuckDB Secrets Manager documentation for more details on managing secrets.

-- Create a secret with MongoDB Atlas credentials
CREATE SECRET mongo_creds (
    TYPE mongo,
    HOST 'cluster0.xxxxx.mongodb.net',
    USER 'myuser',
    PASSWORD 'mypassword',
    SRV 'true'
);

-- Attach using the secret (use readPreference=secondaryPreferred for replica sets)
ATTACH 'dbname=mydb?readPreference=secondaryPreferred' AS atlas_db (TYPE mongo, SECRET 'mongo_creds');

-- Query your collections
SELECT * FROM atlas_db.mydb.mycollection;

Features

Direct SQL queries over MongoDB collections (no ETL/export)
MongoDB Atlas support via connection strings or DuckDB Secrets
TLS/SSL encryption for secure connections
Flexible schema handling: User-provided schemas, __schema document support (Atlas SQL compatibility), or automatic inference
Nested document flattening with underscore-separated names
BSON type mapping including Decimal128, arrays, and nested documents (see BSON Type Mapping)
VARIANT columns that keep the BSON type of every value for heterogeneous or deeply nested fields, per column, as the inference fallback (SET mongo_nested_type = 'variant'), or for whole documents (raw := true) (see VARIANT Columns)
Query pushdown to reduce data transfer (see Pushdown Strategy):
- Filters (WHERE clauses, complex expressions, semi-join IN)
- Projections (SELECT columns)
- Limits and TopN (ORDER BY _id LIMIT N)
- Aggregations (COUNT, SUM, MIN, MAX, AVG with GROUP BY)
Write support: INSERT INTO, CREATE TABLE AS SELECT, COPY TO (FORMAT mongo), and DROP TABLE

Installation

The easiest way to install the mongo extension is from the DuckDB community extensions repository:

INSTALL mongo FROM community;
LOAD mongo;

After installation, you can use the extension as described in the Connecting to MongoDB section.

Note: You can also build from source and load the extension binary directly with LOAD '/path/to/mongo.duckdb_extension';

Connecting to MongoDB

Connection String Format

1. Key-value format:

ATTACH 'host=localhost port=27017' AS mongo_db (TYPE MONGO);
ATTACH 'host=localhost port=27017 dbname=mydb' AS mongo_db (TYPE MONGO);
ATTACH 'host=cluster0.xxxxx.mongodb.net user=myuser password=mypass srv=true' AS atlas_db (TYPE MONGO);

2. MongoDB URI format:

ATTACH 'mongodb://user:pass@localhost:27017/mydb' AS mongo_db (TYPE MONGO);

Connection Parameters:

Name	Description	Default	Applies To Format
`host`	MongoDB hostname or IP address	`localhost`	Both 1 and 2
`port`	MongoDB port number	`27017`	Both 1 and 2
`user` / `username`	MongoDB username	-	Both 1 and 2
`password`	MongoDB password	-	Both 1 and 2
`dbname` / `database`	Specific MongoDB database to connect to	-	Both 1 and 2
`authsource`	Authentication database	-	Both 1 and 2
`srv`	Use SRV connection format (for MongoDB Atlas)	`false`	Both 1 and 2
`tls` / `ssl`	Enable TLS/SSL encryption	`false`	Both 1 and 2
`tls_ca_file`	Path to CA certificate file	-	Both 1 and 2
`tls_allow_invalid_certificates`	Allow invalid certificates (for testing only)	`false`	Both 1 and 2
`options`	Additional MongoDB connection string query parameters (e.g. `readPreference=secondary&maxPoolSize=10`)	-	Format 1 and secrets

Restricting `mongo_scan`

By default, mongo_scan accepts any MongoDB URI (or secret name). That URI is passed to the MongoDB C++ driver, which can open local files via TLS options such as tlsCAFile.

To allow mongo_scan only for catalogs that are already attached:

SET mongo_enable_direct_scan = false;

When this setting is false:

SELECT * FROM attached_mongo.collection still works (views call mongo_scan with the attached URI)
mongo_scan with that exact attached connection string still works
any other URI or secret is rejected before a MongoDB client is created

Set this before locking DuckDB configuration (SET lock_configuration = true). The default is true for backward compatibility.

Tip: For replica sets (including MongoDB Atlas), use readPreference=secondaryPreferred to route reads to secondaries.

TLS/SSL Connections

The extension supports TLS/SSL encrypted connections for secure MongoDB access. Enable TLS by setting tls=true or ssl=true:

-- Basic TLS connection
ATTACH 'host=mongodb.example.com port=27017 user=myuser password=mypass tls=true' AS mongo_secure (TYPE MONGO);

-- TLS with custom CA certificate file
ATTACH 'host=mongodb.example.com port=27017 user=myuser password=mypass tls=true tls_ca_file=/path/to/ca.pem' AS mongo_secure (TYPE MONGO);

Using Secrets with TLS:

CREATE SECRET mongo_tls_secret (
    TYPE mongo,
    HOST 'mongodb.example.com',
    PORT '27017',
    USER 'myuser',
    PASSWORD 'mypass',
    TLS 'true',
    TLS_CA_FILE '/path/to/ca.pem'
);

ATTACH 'dbname=mydb' AS mongo_secure (TYPE mongo, SECRET 'mongo_tls_secret');

Using DuckDB Secrets

Store credentials securely using DuckDB Secrets instead of embedding them in connection strings:

-- Create a secret with MongoDB credentials
CREATE SECRET mongo_creds (
    TYPE mongo,
    HOST 'cluster0.xxxxx.mongodb.net',
    USER 'myuser',
    PASSWORD 'mypassword',
    SRV 'true'
);

-- Attach using the secret (options in ATTACH path merge with secret)
ATTACH 'dbname=mydb?readPreference=secondaryPreferred' AS atlas_db (TYPE mongo, SECRET 'mongo_creds');

URI options in the secret: Use the OPTIONS key to store arbitrary MongoDB URI options alongside the credentials. This is the only way to set options such as readPreference when passing a secret name to mongo_scan:

CREATE SECRET mongo_secondary (
    TYPE mongo,
    HOST 'cluster0.xxxxx.mongodb.net',
    USER 'myuser',
    PASSWORD 'mypassword',
    SRV 'true',
    OPTIONS 'readPreference=secondary&maxPoolSize=10'
);
SELECT * FROM mongo_scan('mongo_secondary', 'mydb', 'mycollection');

Default secret: Create an unnamed secret to use as the default for all ATTACH operations:

CREATE SECRET (TYPE mongo, HOST 'localhost', USER 'myuser', PASSWORD 'mypass');
ATTACH '' AS mongo_db (TYPE mongo);  -- Uses __default_mongo automatically
ATTACH 'dbname=mydb' AS mongo_db (TYPE mongo);  -- Options merge with secret

Note: An explicit database alias (AS alias_name) is required. The dbname parameter specifies which MongoDB database to connect to, not the DuckDB database name.

Entity Mapping

When using ATTACH to connect to MongoDB, the extension maps MongoDB entities to DuckDB entities as follows:

MongoDB Entity          →  DuckDB Entity
─────────────────────────────────────────
MongoDB Instance        →  Catalog (via ATTACH)
MongoDB Database        →  Schema
MongoDB Collection      →  Table/View

Default Schema Behavior:

Without dbname: Creates a schema for each MongoDB database plus a main schema; defaults to "main"
With dbname: Creates only the specified database schema; defaults to that schema

ATTACH 'host=localhost port=27017' AS mongo_all (TYPE MONGO);
USE mongo_all;  -- Defaults to "main", but all databases available as schemas

ATTACH 'host=localhost port=27017 dbname=mydb' AS mongo_db (TYPE MONGO);
USE mongo_db;  -- Defaults to "mydb" (only schema available)

Querying MongoDB

Setting Up Test Data (For Examples)

Prerequisites:

MongoDB instance running (e.g., docker run -d -p 27017:27017 mongo or local MongoDB installation)
mongosh installed

To follow along with the examples in this README, you can create a test database with sample data:

Option 1: Use the test script (recommended)

bash test/create-mongo-tables.sh

Option 2: Manual setup with mongosh

mongosh "mongodb://localhost:27017/duckdb_mongo_test" --eval "db.orders.insertMany([{order_id: 'ORD-001', items: [{product: 'Laptop', quantity: 1, price: 999.99}, {product: 'Mouse', quantity: 2, price: 29.99}], total: 1059.97, status: 'completed'}, {order_id: 'ORD-002', items: [{product: 'Desk', quantity: 1, price: 299.99}], total: 299.99, status: 'pending'}, {order_id: 'ORD-003', items: [], total: 0, status: 'cancelled'}, {order_id: 'ORD-004', items: [{product: 'Keyboard', quantity: 1}], total: 79.99, status: 'pending'}]);"

Option 3: Interactive mongosh

mongosh "mongodb://localhost:27017/duckdb_mongo_test"

Then paste:

db.orders.insertMany([
  { order_id: 'ORD-001', items: [{ product: 'Laptop', quantity: 1, price: 999.99 }, { product: 'Mouse', quantity: 2, price: 29.99 }], total: 1059.97, status: 'completed' },
  { order_id: 'ORD-002', items: [{ product: 'Desk', quantity: 1, price: 299.99 }], total: 299.99, status: 'pending' },
  { order_id: 'ORD-003', items: [], total: 0, status: 'cancelled' },
  { order_id: 'ORD-004', items: [{ product: 'Keyboard', quantity: 1 }], total: 79.99, status: 'pending' }
]);

Basic Queries

-- Attach to MongoDB (using test database from setup above)
ATTACH 'host=localhost port=27017 dbname=duckdb_mongo_test' AS mongo_test (TYPE MONGO);

-- Show attached databases
SHOW DATABASES;
┌───────────────┐
│ database_name │
│    varchar    │
├───────────────┤
│ memory        │
│ mongo_test    │
└───────────────┘

-- List schemas in the attached catalog (only the specified database when using dbname=)
SELECT schema_name FROM information_schema.schemata WHERE catalog_name = 'mongo_test';
┌───────────────────┐
│    schema_name    │
│      varchar      │
├───────────────────┤
│ duckdb_mongo_test │
└───────────────────┘

-- Select data from a specific collection
SELECT order_id, status, total FROM mongo_test.duckdb_mongo_test.orders;
┌──────────┬───────────┬─────────┐
│ order_id │  status   │  total  │
│ varchar  │  varchar  │ double  │
├──────────┼───────────┼─────────┤
│ ORD-001  │ completed │ 1059.97 │
│ ORD-002  │ pending   │  299.99 │
│ ORD-003  │ cancelled │     0.0 │
│ ORD-004  │ pending   │   79.99 │
└──────────┴───────────┴─────────┘

-- Query arrays of objects using list_extract (1-based indexing)
SELECT order_id, list_extract(items, 1).product AS product, list_extract(items, 1).price AS price FROM mongo_test.duckdb_mongo_test.orders;
┌──────────┬──────────┬────────┐
│ order_id │ product  │ price  │
│ varchar  │ varchar  │ double │
├──────────┼──────────┼────────┤
│ ORD-001  │ Laptop   │ 999.99 │
│ ORD-002  │ Desk     │ 299.99 │
│ ORD-003  │ NULL     │   NULL │
│ ORD-004  │ Keyboard │   NULL │
└──────────┴──────────┴────────┘

-- Expand arrays into multiple rows using UNNEST
SELECT order_id, UNNEST(items).product AS product, UNNEST(items).price AS price 
FROM mongo_test.duckdb_mongo_test.orders 
WHERE order_id = 'ORD-001';
┌──────────┬──────────┬─────────┐
│ order_id │ product  │  price  │
│ varchar  │ varchar  │ double  │
├──────────┼──────────┼─────────┤
│ ORD-001  │ Laptop   │  999.99 │
│ ORD-001  │ Mouse    │   29.99 │
└──────────┴──────────┴─────────┘

-- Query with aggregation
SELECT status, COUNT(*) as count, SUM(total) as total_revenue 
  FROM mongo_test.duckdb_mongo_test.orders 
  GROUP BY status
  ORDER BY status;
┌───────────┬───────┬───────────────┐
│  status   │ count │ total_revenue │
│  varchar  │ int64 │    double     │
├───────────┼───────┼───────────────┤
│ cancelled │     1 │           0.0 │
│ completed │     1 │       1059.97 │
│ pending   │     2 │        379.98 │
└───────────┴───────┴───────────────┘

-- Filter on array element fields using UNNEST
SELECT DISTINCT order_id FROM mongo_test.duckdb_mongo_test.orders, UNNEST(items) AS unnest 
WHERE unnest.product = 'Mouse';
┌──────────┐
│ order_id │
│ varchar  │
├──────────┤
│ ORD-001  │
└──────────┘

Using mongo_scan Directly

You can also use the mongo_scan table function directly without attaching:

-- Basic usage with a raw URI
SELECT * FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'mycollection');

-- Using a named secret (recommended for Atlas / SRV connections)
CREATE SECRET my_atlas (
    TYPE MONGO,
    HOST 'cluster0.xxxxx.mongodb.net',
    USER 'myuser',
    PASSWORD 'mypassword',
    SRV 'true'
);
SELECT * FROM mongo_scan('my_atlas', 'mydb', 'mycollection');

-- With filter and sample size
SELECT * FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'mycollection', 
                         filter := '{"status": "active"}', sample_size := 200);

-- With explicit schema
SELECT * FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'mycollection',
                         columns := {'_id': 'VARCHAR', 'name': 'VARCHAR', 'age': 'BIGINT'});

-- With nested path mapping
SELECT * FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'mycollection',
                         columns := {
                             '_id': 'VARCHAR',
                             'name': 'VARCHAR',
                             'city': {'type': 'VARCHAR', 'path': 'address.city'}
                         });

Parameters:

connection_string: MongoDB connection string (mongodb:// or mongodb+srv://) or a secret name created with CREATE SECRET (TYPE MONGO, ...). To set URI options such as readPreference when using a secret, add them to the secret's OPTIONS key (see Using DuckDB Secrets)
database: MongoDB database name
collection: MongoDB collection name
filter (optional): MongoDB query filter as JSON string (e.g., '{"status": "active"}')
sample_size (optional): Number of documents to sample for schema inference (default: the mongo_sample_size setting, which defaults to 100; see Schema Inference Sample Size)
columns (optional): Explicit schema definition as a struct (see Schema Resolution for details)
schema_mode (optional): How to handle type mismatches: 'permissive' (default), 'dropmalformed', or 'failfast' (see Schema Enforcement Modes)
nested_type (optional): How inferred schemas represent nested documents, irregular arrays and type conflicts: 'json' (default: VARCHAR holding JSON text) or 'variant' (DuckDB VARIANT). Defaults to the mongo_nested_type setting (see VARIANT Columns)
raw (optional): true skips schema inference and returns (_id VARCHAR, doc VARIANT) with doc holding the whole document. Cannot be combined with columns (see VARIANT Columns)

Schema Inference Sample Size

Schema inference samples 100 documents per collection by default. For collections with heterogeneous documents, rare fields can be missed. The mongo_sample_size setting raises the sample size for every collection that does not pass an explicit sample_size, including all collections in an attached catalog:

SET mongo_sample_size = 1000;
ATTACH 'host=localhost port=27017 dbname=mydb' AS mongo_db (TYPE MONGO);
SELECT * FROM mongo_db.mycollection;  -- schema inferred from up to 1000 sampled documents

Resolution order: mongo_scan(..., sample_size := N) > SET mongo_sample_size > default of 100. The value must be between 1 and 2147483647; out-of-range values are rejected at SET time.

Things to know:

Set it before the first query, not just before ATTACH. The value is read when a collection's schema is first inferred (on first query), and the result is cached. Changing the setting afterwards has no effect on already-inferred collections until you run mongo_clear_cache() or re-ATTACH.
Only affects inference. Collections with a __schema document, or scans that pass columns, never sample and ignore this setting.
Larger samples slow the first query per collection. Inference uses $sample; when the sample exceeds roughly 5% of the collection, MongoDB falls back to a full collection scan plus a random sort, which can be expensive on large collections.
Bigger is not a guarantee. Sampling is random, so a field present in very few documents can still be missed. For exact schemas, use a __schema document or the columns parameter (see Schema Resolution).

Cache Management

When using ATTACH to connect to MongoDB, the extension caches schema information, collection names, and view metadata to improve query performance. If the MongoDB schema changes (e.g., new collections are added, or collection schemas change), you may need to clear the cache:

-- Clear all MongoDB caches for all attached databases
SELECT * FROM mongo_clear_cache();

This function clears all caches for all attached MongoDB databases:

Collection names cache
View info cache (including schema information)
Schema cache

Note: Currently, cache clearing is all-or-nothing (all databases). Selective cache clearing for specific databases or collections is not yet supported.

After clearing the cache, the next query will re-scan schemas and re-infer collection schemas.

Writing Data

The extension supports writing data to MongoDB collections via INSERT, CREATE TABLE AS, and COPY TO.

INSERT INTO

Insert rows into an existing MongoDB collection:

ATTACH 'host=localhost port=27017 dbname=duckdb_mongo_test' AS m (TYPE MONGO);

-- Insert literal values
INSERT INTO m.duckdb_mongo_test.users SELECT * FROM (VALUES ('alice', 28)) AS t(name, age);

-- Insert from a CSV file
INSERT INTO m.duckdb_mongo_test.users (name, age)
SELECT name, age FROM read_csv('new_users.csv');

-- Insert from another MongoDB collection
INSERT INTO m.duckdb_mongo_test.archived_orders
SELECT * FROM m.duckdb_mongo_test.orders WHERE status = 'completed';

The collection must already exist in MongoDB (schema is inferred from existing documents). Columns are mapped back to nested MongoDB paths using the same flattening rules as reads (e.g., a column named address_city writes to address.city).

CREATE TABLE AS SELECT

Create a new MongoDB collection from a query result:

-- Create a new collection from a query
CREATE TABLE m.duckdb_mongo_test.active_users AS
SELECT name, age, email FROM m.duckdb_mongo_test.users WHERE age > 25;

-- ETL from any DuckDB source into MongoDB
CREATE TABLE m.duckdb_mongo_test.imported_data AS
SELECT * FROM read_csv('data.csv');

The collection is created automatically. Column names become top-level field names in the resulting documents.

COPY TO

Bulk-export query results to a MongoDB collection:

-- Three-part target: alias.database.collection
COPY (SELECT * FROM source_table) TO 'm.duckdb_mongo_test.target_collection' (FORMAT mongo);

-- Two-part target uses the attached database name
COPY (SELECT * FROM source_table) TO 'm.target_collection' (FORMAT mongo);

DROP TABLE

Drop a MongoDB collection:

DROP TABLE m.duckdb_mongo_test.my_collection;
DROP TABLE IF EXISTS m.duckdb_mongo_test.my_collection;

Write Path Notes

Writes use unordered insert_many in batches of 1000 documents for throughput.
If _id is not provided, MongoDB generates an ObjectId automatically.
Duplicate _id values cause an error (the batch partially succeeds for non-duplicate rows).
CREATE TABLE (without AS SELECT) is not supported -- MongoDB collections have no fixed schema.
UPDATE and DELETE are not yet supported.

Reference

BSON Type Mapping

BSON Type	DuckDB Logical Type	Notes
`String`	`VARCHAR`
`Int32`, `Int64`	`BIGINT`
`Double`	`DOUBLE`
`Decimal128`	`DOUBLE`	High-precision decimals converted to double (may lose precision)
`Boolean`	`BOOLEAN`
`Date`	`TIMESTAMP` / `DATE`	`DATE` if time component is midnight UTC, else `TIMESTAMP`
`ObjectId`	`VARCHAR`	24-character hex string
`Binary`	`BLOB`
`Array`	`LIST` or `VARCHAR`	`LIST(STRUCT(...))` for arrays of objects, `LIST(primitive)` for arrays of primitives, `LIST(LIST(...))` for arrays of arrays (see Array Handling)
`Document`	`VARCHAR`	Nested documents stored as JSON string
`Null`, `Undefined`	`VARCHAR`	Type refined from other documents during inference
`Regex`, `Code`, `Symbol`, `Timestamp`, `MinKey`, `MaxKey`	`VARCHAR`	Special BSON types stored as string representation
any	`VARIANT`	Opt-in via an explicit schema, `nested_type := 'variant'`, or `raw := true`; keeps each value's own BSON type (see VARIANT Columns)

Schema Resolution

The extension uses a three-tier schema resolution strategy with the following priority order:

User-provided columns parameter (highest priority)
__schema document in collection (for Atlas SQL compatibility)
Automatic schema inference (fallback)

User-Provided Schema

You can explicitly specify the schema using the columns parameter when calling mongo_scan:

Simple Format:

SELECT * FROM mongo_scan(
    'mongodb://localhost:27017',
    'mydb',
    'mycollection',
    columns := {'_id': 'VARCHAR', 'name': 'VARCHAR', 'age': 'BIGINT', 'active': 'BOOLEAN'}
);

Nested Format with Path Mapping: For nested fields, you can map column names to MongoDB dot notation paths:

SELECT * FROM mongo_scan(
    'mongodb://localhost:27017',
    'mydb',
    'mycollection',
    columns := {
        '_id': 'VARCHAR',
        'name': 'VARCHAR',
        'city': {'type': 'VARCHAR', 'path': 'address.city'},
        'street': {'type': 'VARCHAR', 'path': 'address.street'}
    }
);

The columns parameter accepts:

Simple format: 'column_name': 'TYPE' where TYPE is a DuckDB type string (e.g., 'VARCHAR', 'BIGINT', 'DOUBLE', 'BOOLEAN', 'DATE', 'TIMESTAMP')
Nested format: 'column_name': {'type': 'TYPE', 'path': 'mongo.path'} for mapping to nested MongoDB fields

__schema Document (Atlas SQL Compatibility)

For MongoDB Atlas SQL compatibility, you can store a schema document in your collection with _id: "__schema". The extension will automatically detect and use this schema.

Simple Format (schema fields directly in document):

{
  "_id": "__schema",
  "name": "VARCHAR",
  "age": "BIGINT",
  "email": "VARCHAR"
}

Nested Format (schema in nested schema field):

{
  "_id": "__schema",
  "schema": {
    "name": "VARCHAR",
    "age": "BIGINT",
    "email": "VARCHAR"
  }
}

Path Mapping Format (for nested MongoDB fields):

{
  "_id": "__schema",
  "name": "VARCHAR",
  "city": {"type": "VARCHAR", "path": "address.city"},
  "street": {"type": "VARCHAR", "path": "address.street"}
}

Note: When using ATTACH to connect to MongoDB, the __schema document is cached along with other schema information. Use mongo_clear_cache() to invalidate the cache after schema changes.

Schema Inference

When neither user-provided schema nor __schema document is available, the extension automatically infers schemas by sampling documents (default: 100, configurable per scan via sample_size or globally via SET mongo_sample_size; see Schema Inference Sample Size):

Nested Documents: Flattened with underscore-separated names (e.g., user_address_city), up to 5 levels deep. The parent document is also exposed as a whole (JSON text by default, or VARIANT with nested_type := 'variant'; see VARIANT Columns)
Type Conflicts: Frequency-based resolution:
- VARCHAR if >70% of values are strings
- DOUBLE if ≥30% are doubles (or any doubles present)
- BIGINT if ≥30% are integers (when no doubles)
- BOOLEAN/TIMESTAMP if ≥70% match
- Defaults to VARCHAR
- With nested_type := 'variant', only lossless merges stay typed (BIGINT+DOUBLE to DOUBLE, DATE+TIMESTAMP to TIMESTAMP, same-shaped LISTs and STRUCTs are merged); any other conflict becomes VARIANT
Missing Fields: NULL values

Schema Enforcement Modes

When using an explicit schema (via columns parameter or __schema document), you can control how the extension handles documents that don't match the expected types using the schema_mode parameter:

Mode	Behavior	Use Case
`permissive`	Set invalid fields to NULL (default)	Exploratory analysis, fault-tolerant pipelines
`dropmalformed`	Skip entire rows with schema violations	Data quality filtering, clean datasets
`failfast`	Throw error immediately on first mismatch	Production pipelines, data contracts

Examples:

-- PERMISSIVE (default): Invalid values become NULL
SELECT * FROM mongo_scan(
    'mongodb://localhost:27017', 'mydb', 'mycol',
    columns := {'name': 'VARCHAR', 'age': 'INTEGER'},
    schema_mode := 'permissive'
);

-- DROPMALFORMED: Skip rows where 'age' is not a valid integer
SELECT * FROM mongo_scan(
    'mongodb://localhost:27017', 'mydb', 'mycol',
    columns := {'name': 'VARCHAR', 'age': 'INTEGER'},
    schema_mode := 'dropmalformed'
);

-- FAILFAST: Throw error if any document has invalid 'age' value
SELECT * FROM mongo_scan(
    'mongodb://localhost:27017', 'mydb', 'mycol',
    columns := {'name': 'VARCHAR', 'age': 'INTEGER'},
    schema_mode := 'failfast'
);

Note: Schema enforcement only applies when an explicit schema is provided (via columns or __schema). Inferred schemas use permissive behavior regardless of the schema_mode setting.

When using dropmalformed or failfast, certain query optimizations are disabled to ensure accurate validation (e.g., aggregate pushdowns run in DuckDB instead of MongoDB, and all schema columns are fetched for validation). For best performance with large collections, use permissive (the default) unless strict enforcement is required.

VARIANT Columns

A column typed VARIANT (DuckDB 2.0+) accepts any BSON value and keeps each row's own type and shape, so it fits fields that are heterogeneous, deeply nested, or not fully covered by the sampled schema. VARIANT is opt-in, in three ways:

Declare it in the columns parameter or a __schema document (explicit schema).
nested_type := 'variant' or SET mongo_nested_type = 'variant': schema inference produces VARIANT wherever it would otherwise fall back to JSON text.
raw := true: no inference at all; the scan returns (_id VARCHAR, doc VARIANT).

Explicit schema:

-- A sub-document as one addressable value
SELECT name, specs.cpu::VARCHAR, specs.dimensions.width::INT, variant_keys(specs)
FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'products',
                columns := {'name': 'VARCHAR', 'specs': 'VARIANT'});

-- A field whose type differs per document
SELECT variant_typeof(value), TRY_CAST(value AS BIGINT)
FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'events',
                columns := {'value': 'VARIANT'});

-- The whole document, with the special path $$ROOT
SELECT doc.level1.level2.level3.level4.level5.level6.value::VARCHAR
FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'deep',
                columns := {'doc': {'type': 'VARIANT', 'path': '$$ROOT'}});

Inference with nested_type := 'variant':

Flat scalar fields are inferred exactly as before. The difference is what happens where flattening gives up:

Situation	`nested_type := 'json'` (default)	`nested_type := 'variant'`
Parent of a flattened sub-document (`specs` next to `specs_cpu`)	VARCHAR (JSON text)	VARIANT
Nesting deeper than 5 levels, empty arrays, structures inference cannot type	VARCHAR (JSON text)	VARIANT
Array of primitives with mixed element types (`[1, "two", 3.5]`)	LIST of the first element's type; other elements become NULL	VARIANT
Arrays whose depth differs per document	LIST at the deepest depth; shallower rows are wrapped	VARIANT
Scalar type conflict (`"123"` in one document, `123` in another)	Most frequent type; other rows become NULL	VARIANT
Field that is null in every sampled document	VARCHAR	VARIANT
Documents/arrays inside an array of objects	VARCHAR field in the STRUCT	VARIANT field in the STRUCT

-- Per scan
SELECT name, specs.cpu::VARCHAR, variant_typeof(value)
FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'products', nested_type := 'variant');

-- For every inferred collection, including attached catalogs
SET mongo_nested_type = 'variant';
ATTACH 'host=localhost port=27017 dbname=mydb' AS mongo_db (TYPE MONGO);
SELECT specs.cpu::VARCHAR FROM mongo_db.products;

Resolution order: mongo_scan(..., nested_type := ...) > SET mongo_nested_type > default json. Like mongo_sample_size, the setting is read when a collection's schema is first inferred and the result is cached: run mongo_clear_cache() or re-ATTACH after changing it. Explicit schemas (columns or __schema) ignore it.

Raw scan with raw := true:

SELECT _id, doc.name::VARCHAR, variant_keys(doc)
FROM mongo_scan('mongodb://localhost:27017', 'mydb', 'users', raw := true)
WHERE _id = '507f1f77bcf86cd799439011';

No documents are sampled, so the first query on a large or very irregular collection starts immediately and no field can be missed. _id stays a regular column (ObjectIds as hex strings) so filters on it are pushed down; paths read from doc are projected and pre-filtered server-side as described below. filter and pipeline still apply server-side. raw cannot be combined with columns.

Values map to the VARIANT types you would expect: int32/int64 to INT32/INT64, double to DOUBLE, Date to TIMESTAMP_MILIS, Binary to BLOB, Decimal128 to DECIMAL (or DOUBLE for NaN, infinity, or exponent forms), ObjectId to a VARCHAR hex string, and null/undefined to NULL. Objects list their keys in lexicographic order, so doc::JSON is key-sorted. Writes go the other way: a VARIANT cell in INSERT, CREATE TABLE AS, or COPY TO is serialized as a real BSON subdocument, array, or scalar rather than JSON text.

Pushdown through paths:

When a query reads paths of a VARIANT column (doc.a.b, variant_extract(doc, 'a')), DuckDB pushes the extraction into the scan and only those paths are fetched: SELECT doc.name::VARCHAR, doc.price::DOUBLE FROM ... sends {"name": 1, "price": 1, "_id": 1} as the projection, and a cast on the path is applied as the document is read. Reading the whole column anywhere in the query fetches the whole column.

Predicates on such paths are pushed to MongoDB as a pre-filter that matches at least every row DuckDB's predicate accepts; DuckDB still evaluates the exact predicate on what comes back. The pre-filter accounts for the liberal VARIANT casts ('5' and 5.4 both cast to 5, so TRY_CAST(doc.n AS INT) = 5 becomes {"n": {"$gt": 4, "$lt": 6}} plus the non-numeric values), day ranges for DATE, millisecond bounds for TIMESTAMP, and MongoDB's array traversal for IS NULL. Supported forms are =, <>, <, <=, >, >=, BETWEEN, IN, IS [NOT] NULL on TRY_CAST(path AS T) / path::T for integer, DOUBLE, VARCHAR, BOOLEAN, DATE, and TIMESTAMP targets, variant_typeof(path) = '...', and AND of those (untranslatable terms are dropped from the pre-filter). CALL enable_logging('MongoQuery') shows exactly what was sent.

Notes:

A strict cast such as doc.n::INT throws if any fetched row holds a value that does not convert, as it does for a DuckDB table; use TRY_CAST for mixed fields.
DuckDB only pushes VARIANT extraction into a scan it reads directly (mongo_scan(...) or an attached table), not through a view or macro over it, and it keeps throwing casts such as path::INT above views, so predicates in that form only reach MongoDB on direct scans; TRY_CAST is pushed either way.
A bare comparison such as doc.n = 5 compares VARIANT values in DuckDB and is not pushed; cast the path to the intended type.
GROUP BY and ORDER BY on VARIANT columns run in DuckDB. COUNT(col) is still pushed down (it counts non-null BSON values).

Array Handling

Arrays of Objects:

Arrays of objects are stored as DuckDB LIST(STRUCT(...)) types
Schema Inference: Scans up to 10 elements per array to discover all field names across array elements
- This ensures fields that only exist in later elements are still discovered
- Example: If items[0] has {product, quantity} and items[5] has {product, quantity, discount}, the discount field will be included in the STRUCT
- Creates a LIST type containing a STRUCT with all discovered fields
Querying Arrays: Use list_extract() to access specific elements (1-based indexing) or UNNEST() to expand arrays into multiple rows. See Basic Queries for examples.

Arrays of Primitives:

Arrays of primitives (strings, numbers) are stored as LIST types
Example: tags: ['admin', 'user'] → LIST(VARCHAR) containing ['admin', 'user']
Can be queried with list_extract (1-based indexing): list_extract(tags, 1) returns 'admin'
Can be expanded with UNNEST: SELECT UNNEST(tags) FROM mongo_test.duckdb_mongo_test.users

Arrays of Arrays:

Arrays of arrays are stored as LIST(LIST(...)) types
Supports nested arrays of any depth (up to 5 levels)
Example: matrix: [[1,2], [3,4]] → LIST(LIST(BIGINT)) containing [[1,2],[3,4]]
Example: data: [[[1,2], [3,4]], [[5,6], [7,8]]] → LIST(LIST(LIST(BIGINT))) for 3D arrays
Arrays of arrays of objects: data: [[{x: 1}, {x: 2}], [{x: 3}, {x: 4}]] → LIST(LIST(STRUCT(...)))
Can be queried with nested list_extract (1-based indexing):
- For 2D arrays: list_extract(list_extract(matrix, 1), 2) returns 2 (second element of first row)
- For 3D arrays: list_extract(list_extract(list_extract(data, 1), 1), 2) returns 2 (second element of first row of first layer)

Mixed Array Depths:

When documents in a collection have arrays of different depths, the schema inference uses the deepest depth found across all sampled documents
Documents with shallower arrays are automatically wrapped to match the expected depth, allowing all arrays to be returned as DuckDB LIST types
Example: If one document has data: [[[1,2], [3,4]]] (3D) and another has data: [[1,2], [3,4]] (2D), the schema infers LIST(LIST(LIST(BIGINT))) (3D)
- The 2D array [[1,2], [3,4]] is automatically wrapped to [[[1,2]], [[3,4]]] to match the 3D schema
- Both documents return valid LIST values that can be queried using DuckDB's LIST functions
This ensures data is preserved and queryable even when array structures vary across documents

Limitations

No UPDATE or DELETE -- write support covers INSERT, CTAS, COPY TO, and DROP TABLE
Schema inference (when used as fallback) samples documents and may miss fields that don't appear in the sample
Schema re-inferred per query when using mongo_scan directly (cached when using ATTACH; use mongo_clear_cache() to invalidate)
Decimal128 precision: Converted to DOUBLE, which may lose precision for high-precision decimal values
Nested documents in arrays: Stored as VARCHAR (JSON strings) rather than nested STRUCT types
- Example: items: [{product: 'Laptop', specs: {cpu: 'Intel', ram: '16GB'}}] → specs field is VARCHAR, not STRUCT
- Declaring the column as VARIANT in an explicit schema, or scanning with nested_type := 'variant', preserves the full nested structure (see VARIANT Columns)

Advanced Topics

Architecture

The extension enables in-process analytical SQL queries and writes over MongoDB data using DuckDB's embedded analytical engine. Reads execute against live MongoDB data in real-time, with analytical operations (joins, aggregations, window functions) performed locally in memory. Writes serialize DuckDB rows to BSON and batch-insert them via the MongoDB driver.

┌─────────────────────────────────────────┐
│           User/Application              │
└────────┬───────────────────────┬────────┘
         │                       ▲
         │ SQL Query             │ Result set (columnar)
         ▼                       │
┌─────────────────────────────────────────┐
│         DUCKDB ENGINE                   │
│  ┌───────────────────────────────────┐  │
│  │ Query Planning & Optimization     │  │
│  │ - Pushdown & Plan Optimization    │  │
│  └───────────────────────────────────┘  │
│  ┌───────────────────────────────────┐  │
│  │ Query Execution                   │  │
│  │ - Joins, Aggregations             │  │
│  │ - Window Functions, CTEs          │  │
│  └───────────────────────────────────┘  │
└────────┬───────────────────────┬────────┘
         │                       ▲
         │ mongo_scan            │ DataChunks
         ▼                       │
┌────────┴───────────────────────┴────────┐
│ duckdb-mongo Extension                  │
│  • Schema Resolution                    │
│  • Read: Pushdown Optimization          │
│  • Read: BSON → Columnar Conversion     │
│  • Write: Columnar → BSON, insert_many  │
└────────┬───────────────────────┬────────┘
         │                       ▲
         │ MQL / insert_many     │ BSON stream
         ▼                       │
┌─────────────────────────────────────────┐
│         MONGODB DATABASE                │
│  ┌───────────────────────────────────┐  │
│  │ Document Store Operations         │  │
│  │ - Query Execution ($match, $group)│  │
│  │ - Document Streaming (cursor)     │  │
│  │ - Bulk Writes (insert_many)       │  │
│  └───────────────────────────────────┘  │
└─────────────────────────────────────────┘

mongo_scan Execution Flow

┌─────────────────────────────────────────────────────────────────┐
│                    mongo_scan Execution                         │
└─────────────────────────────────────────────────────────────────┘

1. BIND PHASE (happens once per query)
   ┌────────────────────────────────────────────────────────────┐
   │ Parse connection string, database, collection              │
   │ Create MongoDB connection                                  │
   │                                                            │
   │ Schema Resolution:                                         │
   │   • User-provided, __schema document, or inference         │
   │   • Build column names and types                           │
   │                                                            │
   │ Return schema to DuckDB                                    │
   └────────────────────────────────────────────────────────────┘
                              │
                              ▼
2. INIT PHASE (happens once per query)
   ┌────────────────────────────────────────────────────────────┐
   │ Build MongoDB query:                                       │
   │   • Filter pushdown ($match)                               │
   │   • Projection pushdown                                    │
   │   • Aggregation/Limit pushdown ($group, $limit)            │
   │                                                            │
   │ Create MongoDB cursor or aggregation pipeline              │
   └────────────────────────────────────────────────────────────┘
                              │
                              ▼
3. EXECUTION PHASE (called repeatedly for each chunk)
   ┌────────────────────────────────────────────────────────────┐
   │ Fetch documents from cursor:                               │
   │   • Retrieve BSON documents from MongoDB                   │
   │                                                            │
   │ For each document:                                         │
   │   • Parse BSON structure                                   │
   │   • Extract fields by path                                 │
   │   • Convert BSON types → DuckDB types                      │ 
   │   • Flatten nested structures                              │
   │   • Write to columnar DataChunk                            │
   │                                                            │
   │ Return chunk to DuckDB (up to STANDARD_VECTOR_SIZE rows)   │
   └────────────────────────────────────────────────────────────┘
                              │
                              ▼
   ┌────────────────────────────────────────────────────────────┐
   │ DuckDB processes chunk:                                    │
   │   • Filters already applied in MongoDB (via pushdown)      │
   │   • Performs aggregations, joins, etc.                     │
   │   • Requests next chunk if needed                          │
   └────────────────────────────────────────────────────────────┘

Pushdown Strategy

The extension uses a selective pushdown strategy: filter at MongoDB (reduce data transfer), analyze in DuckDB (analytical operations).

Pushed Down to MongoDB:

WHERE clauses (automatic conversion to MongoDB $match queries)
Column projections (only columns used in SELECT are fetched)
LIMIT clauses: simple LIMIT N (cursor limit) and ORDER BY _id LIMIT N (aggregation pipeline)
Manual filter parameter (for MongoDB-specific operators like $elemMatch)
Aggregations: COUNT(*), COUNT(col), SUM, MIN, MAX, AVG with optional GROUP BY (see Aggregation Pushdown)

Kept in DuckDB:

Joins, window functions, CTEs, subqueries
ORDER BY (except ORDER BY _id LIMIT N which is pushed down)

Automatic Filter Pushdown

WHERE clauses are automatically converted to MongoDB $match queries. Use EXPLAIN to see which operations are pushed down:

-- Filter pushed down to MongoDB
EXPLAIN SELECT order_id, status FROM mongo_test.duckdb_mongo_test.orders WHERE status = 'completed';

The plan shows filters and projections in MONGO_SCAN, indicating pushdown:

┌─────────────────────────────┐
│┌───────────────────────────┐│
││       Physical Plan       ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│          order_id         │
│           status          │
│                           │
│           ~1 row          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│        MONGO_SCAN         │
│    ────────────────────   │
│    Function: MONGO_SCAN   │
│                           │
│        Projections:       │
│           status          │
│          order_id         │  ← Only SELECT columns fetched
│                           │
│          Filters:         │
│     status='completed'    │  ← Pushed to MongoDB
│                           │
│           ~1 row          │
└───────────────────────────┘

For aggregations, filters are pushed down while aggregation happens in DuckDB:

Supported Filter Operations:

Comparison operators: =, !=, <, <=, >, >=, BETWEEN (!= becomes {$nin: [value, null]} so that, as in SQL, rows whose field is null or missing are not returned)
IN clauses: WHERE status IN ('active', 'pending') → MongoDB {status: {$in: ['active', 'pending']}}
NULL checks: IS NULL and IS NOT NULL
Multiple conditions: AND/OR combinations merged into efficient MongoDB queries
Nested fields: Flattened fields (e.g., address_city) converted to dot notation (address.city); struct fields of STRUCT columns declared via columns := (e.g., address.city with 'address': 'STRUCT(city VARCHAR)') are pushed the same way
Complex filters: Function calls (e.g., LENGTH(name) > 5) and column-to-column comparisons (e.g., age > balance) pushed down as MongoDB $expr queries (see Complex Filter Pushdown)

Filters on columns whose type has no faithful MongoDB encoding (lists, whole structs, JSON, UUID, HUGEINT, ...) are evaluated by DuckDB after the scan.

To see the exact commands a scan sends, enable the MongoQuery log type:

CALL enable_logging('MongoQuery');
SELECT name FROM mongo_test.duckdb_mongo_test.users WHERE age IN (25, 35);
SELECT message FROM duckdb_logs WHERE type = 'MongoQuery';
-- { "database" : "duckdb_mongo_test", "collection" : "users", "find" : { "filter" : { "age" : { "$in" : [ 25, 35 ] } }, "projection" : { "name" : 1, "_id" : 1 } } }

Examples:

-- Equality (using test data)
SELECT * FROM mongo_test.duckdb_mongo_test.orders WHERE status = 'completed';
-- MongoDB: {status: 'completed'}

-- Range (example with users collection)
SELECT * FROM mongo_test.duckdb_mongo_test.users WHERE age > 28 AND age < 40;
-- MongoDB: {age: {$gt: 28, $lt: 40}}

-- IN
SELECT * FROM mongo_test.duckdb_mongo_test.orders WHERE status IN ('completed', 'pending');
-- MongoDB: {status: {$in: ['completed', 'pending']}}

-- Nested field (example with users collection)
SELECT * FROM mongo_test.duckdb_mongo_test.users WHERE address_city = 'New York';
-- MongoDB: {'address.city': 'New York'}

Note: When using mongo_scan directly, you can provide an optional filter parameter (e.g., filter := '{"status": "active"}') for MongoDB-specific operators. If both WHERE clauses and filter are present, both apply (they are combined with $and).

Note: Filters on array elements (using UNNEST) are not pushed down to MongoDB—they are applied in DuckDB after expanding arrays. This means all documents are fetched from MongoDB, then filtered in DuckDB. For large collections, consider using MongoDB's $elemMatch operator via the filter parameter in mongo_scan to filter at the database level. See Basic Queries for array filtering examples.

LIMIT Pushdown

LIMIT is automatically pushed down when directly above the table scan:

SELECT * FROM mongo_test.duckdb_mongo_test.orders LIMIT 10;
-- MongoDB uses: .limit(10)

Note: When ORDER BY _id is present with LIMIT, the extension uses TopN pushdown via aggregation pipeline (see TopN Pushdown). For other ORDER BY columns, sorting is performed in DuckDB after fetching data.

Projection Pushdown

Projection pushdown automatically fetches only the columns used in the SELECT clause, reducing data transfer and serialization overhead.

How it works:

DuckDB analyzes the query to identify which columns are needed
Only those columns are included in the MongoDB projection
Reduces network transfer and BSON parsing overhead

Example:

-- Only fetches order_id, status, and total (not items or other columns)
SELECT order_id, status, total FROM mongo_test.duckdb_mongo_test.orders WHERE status = 'completed';
-- MongoDB projection: {order_id: 1, status: 1, total: 1, _id: 1}

Use EXPLAIN to see which columns are projected:

EXPLAIN SELECT order_id, status FROM mongo_test.duckdb_mongo_test.orders;

The plan shows Projections: order_id, status in the MONGO_SCAN operator.

Filter Prune Optimization

Filter prune works together with projection pushdown to further reduce data transfer by excluding filter columns that are not used in the SELECT clause when filters are pushed down to MongoDB.

How it works:

Filters are pushed down to MongoDB (server-side filtering)
Filter columns not in the SELECT clause are excluded from the projection
Only columns needed for query results are fetched

Example:

-- Filters on status and total, but only selects order_id
SELECT order_id 
FROM mongo_test.duckdb_mongo_test.orders 
WHERE status = 'completed' AND total > 500;
-- MongoDB: Filters pushed down, but only order_id is fetched
-- status and total are NOT fetched (filtered server-side)

Use EXPLAIN to verify filter prune is working. The plan shows filters that are pushed down but not included in projections:

EXPLAIN SELECT order_id FROM mongo_test.duckdb_mongo_test.orders WHERE status = 'completed' AND total > 500;

The plan shows Projections: order_id and Filters: status='completed' AND total>500.0 in the MONGO_SCAN operator, indicating filters are pushed down but filter columns are not fetched.

Complex Filter Pushdown

Complex filter pushdown enables pushing complex filter expressions (function calls, column-to-column comparisons, etc.) to MongoDB using $expr queries. This allows MongoDB to filter server-side for expressions that cannot be handled by simple TableFilter pushdown.

Supported Complex Filter Types:

Function calls: LENGTH(name) > 5, CHAR_LENGTH(name) > 5
Substring filters: SUBSTRING(name, 1, 3) = 'Ann' (constant start/length only)
Column-to-column comparisons: age > balance, price >= cost
Combined simple and complex filters: age > 25 AND LENGTH(name) > 5

How it works:

Simple filters (column-to-constant comparisons like age > 25) are handled by TableFilter pushdown, which produces faster MongoDB native queries
Complex filters (function calls, column-to-column comparisons) are converted to MongoDB $expr format and pushed down to MongoDB
Both simple and complex filters can be combined in a single query, with each handled by the appropriate pushdown mechanism

Examples:

-- Function call filter (complex) - pushed down as $expr
SELECT name, email FROM mongo_test.duckdb_mongo_test.users WHERE LENGTH(name) > 5;
-- MongoDB: {$expr: {$gt: [{$strLenCP: "$name"}, 5]}}

-- Substring filter (complex) - pushed down as $expr
SELECT name FROM mongo_test.duckdb_mongo_test.users WHERE SUBSTRING(name, 1, 3) = 'Ann';
-- MongoDB: {$expr: {$eq: [{$substrCP: ["$name", 0, 3]}, "Ann"]}}

-- Column-to-column comparison (complex) - pushed down as $expr
SELECT name, age, balance FROM mongo_test.duckdb_mongo_test.users WHERE age > balance;
-- MongoDB: {$expr: {$gt: ["$age", "$balance"]}}

-- Combined simple and complex filters
SELECT name FROM mongo_test.duckdb_mongo_test.users WHERE age > 25 AND LENGTH(name) > 5;
-- MongoDB: {age: {$gt: 25}, $expr: {$gt: [{$strLenCP: "$name"}, 5]}}

Use EXPLAIN to verify complex filter pushdown:

EXPLAIN SELECT name FROM mongo_test.duckdb_mongo_test.users WHERE LENGTH(name) > 5;

The plan shows MONGO_SCAN directly (no FILTER operator above it), indicating the complex filter was pushed down to MongoDB.

Note: Complex filter pushdown works alongside simple filter pushdown. Simple filters are always handled by TableFilter pushdown for optimal performance, while complex filters are handled by $expr pushdown when needed.

Note: Substring pushdown requires constant start and length arguments (SUBSTRING(col, start, length)), with start >= 1 and length >= 0.

Semi-Join IN Filter Pushdown

Semi-join IN filter pushdown enables DuckDB to push IN filters from semi-joins (subqueries) to MongoDB as $in queries. This optimization works automatically when DuckDB's JoinFilterPushdownOptimizer determines that a semi-join's build side is small enough to push as an IN filter.

How it works:

DuckDB builds a hash table from the subquery (e.g., SELECT p_partkey FROM part WHERE p_name LIKE 'forest%')
If the hash table is small, DuckDB generates an IN filter with the matching values
The extension pushes this IN filter to MongoDB as a $in query

Example:

-- Query with IN subquery (semi-join)
SELECT ps_suppkey, ps_availqty
FROM partsupp
WHERE ps_partkey IN (
    SELECT p_partkey FROM part WHERE p_name LIKE 'forest%'
);

The extension automatically pushes the ps_partkey IN (...) filter to MongoDB as:

{"ps_partkey": {"$in": [123, 456, 789, ...]}}

Supported Filter Types:

The extension supports the following DuckDB filter types for pushdown:

Filter Type	MongoDB Equivalent	Description
`CONSTANT_COMPARISON`	`$eq`, `$ne`, `$lt`, `$lte`, `$gt`, `$gte`	Basic comparisons
`IN_FILTER`	`$in`	Value in list
`IS_NULL`	`{field: null}`	Null check
`IS_NOT_NULL`	`{$ne: null}`	Not null check
`CONJUNCTION_AND`	Merged conditions	AND of filters
`CONJUNCTION_OR`	`$or` / `$in`	OR of filters
`STRUCT_EXTRACT`	Dot notation (`a.b.c`)	Nested field access
`OPTIONAL_FILTER`	Unwraps child	Semi-join IN pushdown
`DYNAMIC_FILTER`	Unwraps child	Runtime filter pushdown

Aggregation Pushdown

Aggregation pushdown enables pushing COUNT, SUM, MIN, MAX, AVG aggregates (with optional GROUP BY) to MongoDB as aggregation pipelines. This reduces data transfer by computing aggregates server-side rather than fetching all documents to DuckDB.

Supported Aggregates:

Function	MongoDB Pipeline	Notes
`COUNT(*)`	`$count`	Pushed as optimized `$count` stage
`COUNT(col)`	`$group` + `$sum` + `$cond`	Counts non-null values
`SUM(col)`	`$group` + `$sum`
`MIN(col)`	`$group` + `$min`
`MAX(col)`	`$group` + `$max`
`AVG(col)`	`$group` + `$avg`

Requirements for Aggregation Pushdown:

Aggregate functions must use direct column references (no expressions like SUM(price * quantity))
GROUP BY keys must be direct column references
No DISTINCT, FILTER, or ORDER BY within aggregates
Single grouping set only (no GROUPING SETS, ROLLUP, or CUBE)

Examples:

-- COUNT(*) pushed down as $count pipeline
SELECT COUNT(*) FROM mongo_test.duckdb_mongo_test.users WHERE active = true;
-- MongoDB pipeline: [{$match: {active: true}}, {$count: "count"}]

-- GROUP BY with aggregates pushed down as $group pipeline
SELECT status, COUNT(*), SUM(total) FROM mongo_test.duckdb_mongo_test.orders GROUP BY status;
-- MongoDB pipeline: [{$group: {_id: {status: "$status"}, __agg0: {$sum: 1}, __agg1: {$sum: "$total"}}}, ...]

Use EXPLAIN to verify aggregation pushdown:

EXPLAIN SELECT COUNT(*) FROM mongo_test.duckdb_mongo_test.users;

The plan shows MONGO_SCAN with scan_method: aggregate and pipeline containing $count or $group, indicating the aggregation was pushed down to MongoDB.

TopN Pushdown

TopN pushdown enables pushing ORDER BY _id LIMIT N queries to MongoDB as aggregation pipelines with $sort and $limit stages. This is particularly efficient for paginated queries ordered by the indexed _id field.

Requirements for TopN Pushdown:

Must order by _id column only (MongoDB's indexed primary key)
Must have a LIMIT clause (no offset)
No intermediate operations between ORDER BY and the table scan (projections are allowed)

Example:

-- TopN pushed down as $sort + $limit pipeline
SELECT _id, name FROM mongo_test.duckdb_mongo_test.users ORDER BY _id LIMIT 10;
-- MongoDB pipeline: [{$sort: {_id: 1}}, {$limit: 10}]

Use EXPLAIN to verify TopN pushdown:

EXPLAIN SELECT _id FROM mongo_test.duckdb_mongo_test.users ORDER BY _id LIMIT 5;

The plan shows MONGO_SCAN with scan_method: aggregate and pipeline containing $sort and $limit, indicating the TopN operation was pushed down to MongoDB.

Note: TopN pushdown is conservative and only applies to ORDER BY _id queries. This ensures MongoDB can use its indexed _id field efficiently. Other ORDER BY columns are processed in DuckDB after fetching data.

Contributing

Contributions are welcome! Please open an issue or submit a pull request.

Building from Source

Prerequisites:

CMake 3.5 or higher
C++ compiler with C++17 support
vcpkg (for dependency management)

Build Steps:

Clone the repository with submodules:

git clone --recurse-submodules https://github.com/stephaniewang526/duckdb-mongo.git
cd duckdb-mongo

Set up vcpkg (if not already done):

git clone https://github.com/Microsoft/vcpkg.git
cd vcpkg
./bootstrap-vcpkg.sh  # On Windows: .\bootstrap-vcpkg.bat
export VCPKG_TOOLCHAIN_PATH=`pwd`/scripts/buildsystems/vcpkg.cmake

Install dependencies (first time only):

# Install MongoDB C++ driver via vcpkg
../vcpkg/vcpkg install --triplet arm64-osx  # or x64-osx for Intel Mac

Build the extension:

# Set vcpkg environment
export VCPKG_TOOLCHAIN_PATH=../vcpkg/scripts/buildsystems/vcpkg.cmake
export VCPKG_TARGET_TRIPLET=arm64-osx  # or x64-osx for Intel Mac

# Build
make release

Or use the build script:

bash scripts/build.sh

Built binaries:

./build/release/duckdb - DuckDB shell with the extension pre-loaded
./build/release/test/unittest - Test runner
./build/release/extension/mongo/mongo.duckdb_extension - Loadable extension binary

Loading the Extension (Development)

./build/release/duckdb  # Extension auto-loaded

Or load explicitly:

LOAD '/path/to/mongo.duckdb_extension';

Running Tests

# Set up test database
bash test/create-mongo-tables.sh

# Run tests
MONGODB_TEST_DATABASE_AVAILABLE=1 make test_release

License

See LICENSE file for details.

Third-Party Licenses

This project uses the MongoDB C++ Driver (mongocxx and bsoncxx), which is licensed under the Apache License, Version 2.0. See the NOTICE file for third-party license information and attributions.

What you can do

Measured star growth

About

README.md

duckdb-mongo

Announcement

Quick Start

Features

Installation

Connecting to MongoDB

Connection String Format

Restricting mongo_scan

TLS/SSL Connections

Using DuckDB Secrets

Entity Mapping

Querying MongoDB

Setting Up Test Data (For Examples)

Basic Queries

Using mongo_scan Directly

Schema Inference Sample Size

Cache Management

Writing Data

INSERT INTO

CREATE TABLE AS SELECT

COPY TO

DROP TABLE

Write Path Notes

Reference

BSON Type Mapping

Schema Resolution

User-Provided Schema

__schema Document (Atlas SQL Compatibility)

Schema Inference

Schema Enforcement Modes

VARIANT Columns

Array Handling

Limitations

Advanced Topics

Architecture

mongo_scan Execution Flow

Pushdown Strategy

Automatic Filter Pushdown

LIMIT Pushdown

Projection Pushdown

Filter Prune Optimization

Complex Filter Pushdown

Semi-Join IN Filter Pushdown

Aggregation Pushdown

TopN Pushdown

Contributing

Building from Source

Loading the Extension (Development)

Running Tests

License

Third-Party Licenses

Restricting `mongo_scan`