title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/mongodb.md)

MongoDB engine is read-only table engine which allows to read data from a remote [MongoDB](https://www.mongodb.com/) collection.

Only MongoDB v3.6+ servers are supported.
[Seed list(`mongodb+srv`)](https://www.mongodb.com/docs/manual/reference/glossary/#std-term-seed-list) is not yet supported.

## Creating a table[​](#creating-a-table "Direct link to Creating a table")

```
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MongoDB(host:port, database, collection, user, password[, options[, oid_columns]]);
```

**Engine Parameters**

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `host:port` MongoDB server address.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `database` Remote database name.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `collection` Remote collection name.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `user` MongoDB user.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `password` User password.|  |  |  |  | | --- | --- | --- | --- | | `options` Optional. MongoDB connection string [options](https://www.mongodb.com/docs/manual/reference/connection-string-options/#connection-options) as a URL formatted string. e.g. `'authSource=admin&ssl=true'`| `oid_columns` Comma-separated list of columns that should be treated as `oid` in the WHERE clause. `_id` by default. | | | | | | | | | | | | | | | |

Tip

If you are using the MongoDB Atlas cloud offering connection url can be obtained from 'Atlas SQL' option.
Seed list(`mongodb**+srv**`) is not yet supported, but will be added in future releases.

Alternatively, you can pass a URI:

```
ENGINE = MongoDB(uri, collection[, oid_columns]);
```

**Engine Parameters**

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `uri` MongoDB server's connection URI.|  |  |  |  | | --- | --- | --- | --- | | `collection` Remote collection name.|  |  | | --- | --- | | `oid_columns` Comma-separated list of columns that should be treated as `oid` in the WHERE clause. `_id` by default. | | | | | | | |

## Types mappings[​](#types-mappings "Direct link to Types mappings")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| MongoDB ClickHouse|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | bool, int32, int64 *any numeric type except Decimals*, Boolean, String| double Float64, String|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | date Date, Date32, DateTime, DateTime64, String|  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | string String, *any numeric type(except Decimals) if formatted correctly*| document String(as JSON)|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | array Array, String(as JSON)|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | oid String|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | binary String if in column, base64 encoded string if in an array or document|  |  |  |  | | --- | --- | --- | --- | | uuid (binary subtype 4) UUID|  |  | | --- | --- | | *any other* String | | | | | | | | | | | | | | | | | | | | | |

If key is not found in MongoDB document (for example, column name doesn't match), default value or `NULL` (if the column is nullable) will be inserted.

### OID[​](#oid "Direct link to OID")

If you want a `String` to be treated as `oid` in the WHERE clause, just put the column's name in the last argument of the table engine.
This may be necessary when querying a record by the `_id` column, which by default has `oid` type in MongoDB.
If the `_id` field in the table has other type, for example `uuid`, you need to specify empty `oid_columns`, otherwise the default value for this parameter `_id` is used.

```
db.sample_oid.insertMany([
    {"another_oid_column": ObjectId()},
]);

db.sample_oid.find();
[
    {
        "_id": {"$oid": "67bf6cc44ebc466d33d42fb2"},
        "another_oid_column": {"$oid": "67bf6cc40000000000ea41b1"}
    }
]
```

By default, only `_id` is treated as `oid` column.

```
CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('mongodb://user:pass@host/db', 'sample_oid');

SELECT count() FROM sample_oid WHERE _id = '67bf6cc44ebc466d33d42fb2'; --will output 1.
SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; --will output 0
```

In this case the output will be `0`, because ClickHouse doesn't know that `another_oid_column` has `oid` type, so let's fix it:

```
CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('mongodb://user:pass@host/db', 'sample_oid', '_id,another_oid_column');

-- or

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('host', 'db', 'sample_oid', 'user', 'pass', '', '_id,another_oid_column');

SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; -- will output 1 now
```

## Supported clauses[​](#supported-clauses "Direct link to Supported clauses")

Only queries with simple expressions are supported (for example, `WHERE field = <constant> ORDER BY field2 LIMIT <constant>`).
Such expressions are translated to MongoDB query language and executed on the server side.
You can disable all these restriction, using [mongodb\_throw\_on\_unsupported\_query](/docs/operations/settings/settings#mongodb_throw_on_unsupported_query).
In that case ClickHouse tries to convert query on best effort basis, but it can lead to full table scan and processing on ClickHouse side.

Note

It's always better to explicitly set type of literal because Mongo requires strict typed filters.  
For example you want to filter by `Date`:

```
SELECT * FROM mongo_table WHERE date = '2024-01-01'
```

This will not work because Mongo will not cast string to `Date`, so you need to cast it manually:

```
SELECT * FROM mongo_table WHERE date = '2024-01-01'::Date OR date = toDate('2024-01-01')
```

This applied for `Date`, `Date32`, `DateTime`, `Bool`, `UUID`.

## Usage example[​](#usage-example "Direct link to Usage example")

Assuming MongoDB has [sample\_mflix](https://www.mongodb.com/docs/atlas/sample-data/sample-mflix) dataset loaded

Create a table in ClickHouse which allows to read data from MongoDB collection:

```
CREATE TABLE sample_mflix_table
(
    _id String,
    title String,
    plot String,
    genres Array(String),
    directors Array(String),
    writers Array(String),
    released Date,
    imdb String,
    year String
) ENGINE = MongoDB('mongodb://<USERNAME>:<PASSWORD>@atlas-sql-6634be87cefd3876070caf96-98lxs.a.query.mongodb.net/sample_mflix?ssl=true&authSource=admin', 'movies');
```

Query:

```
SELECT count() FROM sample_mflix_table
```

```
   ┌─count()─┐
1. │   21349 │
   └─────────┘
```

```
-- JSONExtractString cannot be pushed down to MongoDB
SET mongodb_throw_on_unsupported_query = 0;