Key features v5

These are the key features of the MongoDB Foreign Data Wrapper.

Writable FDW

The MongoDB Foreign Data Wrapper lets you modify data on a MongoDB server. You can insert, update, and delete data in the remote MongoDB collections by inserting, updating and deleting data locally in foreign tables.

For more information, see:

WHERE clause pushdown

MongoDB Foreign Data Wrapper allows the pushdown of the WHERE clause only when clauses include the comparison expressions that have a column and a constant as arguments. WHERE clause pushdown isn't supported where the constant is an array. From version 5.5.0 and later, MongoDB Foreign Data Wrapper supports recursive operator expressions, Boolean expressions, relabel types, and vars on both sides of an operator.

For more information, see Example: WHERE clause pushdown.

Join pushdown

MongoDB Foreign Data Wrapper supports pushdown for inner joins, left joins, and right joins. Currently, joins involving only relational and arithmetic operators in join clauses are pushed down to avoid any potential join failures.

For more information, see Example: Join pushdown.

Aggregate pushdown

MongoDB Foreign Data Wrapper supports aggregate pushdown. It pushes the aggregates to the remote MongoDB server instead of fetching all of the rows and aggregating them locally. This gives a very good performance boost for the cases where aggregates can be pushed down. The pushdown is currently limited to aggregate functions min, max, sum, avg, and count, to avoid pushing down the functions that are not present on the MongoDB server. The aggregate filters, orders, variadic and distinct are not pushed down.

For more information, see Example: Aggregate pushdown.

ORDER BY pushdown

MongoDB Foreign Data Wrapper supports ORDER BY pushdown. If possible, push the ORDER BY clause to the remote server. This approach provides the ordered result set from the foreign server, which can help to have an efficient merge join. NULLs behavior is opposite on the MongoDB server. To get an equivalent result, push down ORDER BY with either ASC NULLS FIRST or DESC NULLS LAST. As MongoDB sorts only on fields, only column names in ORDER BY expressions are pushed down.

For more information, see Example: ORDER BY pushdown.

LIMIT OFFSET pushdown

MongoDB Foreign Data Wrapper supports LIMIT/OFFSET pushdown. Wherever possible, perform LIMIT and OFFSET operations on the remote server. This reduces network traffic between local Postgres and remote MongoDB servers.

For more information, see Example: LIMIT OFFSET pushdown.

Connection pooling

The MongoDB Foreign Data Wrapper establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept and reused for subsequent queries in the same session.

Automated cleanup

The MongoDB Foreign Data Wrapper allows the cleanup of foreign tables in a single operation using the DROP EXTENSION command. This feature is especially useful when a foreign table was created for a temporary purpose. The syntax of a DROP EXTENSION command is:

DROP EXTENSION mongo_fdw CASCADE;

For more information, see DROP EXTENSION.

Full-document retrieval

This feature lets you retrieve documents along with all their fields from collection without any knowledge of the fields in the BSON document available in MongoDB's collection. Those retrieved documents are in JSON format.

You can retrieve all available fields in a collection residing in MongoDB Foreign Data Wrapper as explained in the following example.

Example

> db.warehouse.find();
{ "_id" : ObjectId("58a1ebbaf543ec0b90545859"), "warehouse_id" : 1, "warehouse_name" : "UPS", "warehouse_created" : ISODate("2014-12-12T07:12:10Z") }
{ "_id" : ObjectId("58a1ebbaf543ec0b9054585a"), "warehouse_id" : 2, "warehouse_name" : "Laptop", "warehouse_created" : ISODate("2015-11-11T08:13:10Z") }

Steps for retrieving the document:

Create a foreign table with a column name __doc. The type of the column can be json, jsonb, text, or varchar.

CREATE FOREIGN TABLE test_json(__doc json) SERVER mongo_server OPTIONS (database 'testdb', collection 'warehouse');

Retrieve the document.

SELECT * FROM test_json ORDER BY __doc::text COLLATE "C";

The output:

edb=#SELECT * FROM test_json ORDER BY __doc::text COLLATE "C";

Output

                                                                      __doc                                                                  ---------------------------------------------------------------------------------------------------------------------------------------------------------
{ "_id" : { "$oid" : "58a1ebbaf543ec0b90545859" }, "warehouse_id" : 1, "warehouse_name" : "UPS", "warehouse_created" : { "$date" : 1418368330000 } }
{ "_id" : { "$oid" : "58a1ebbaf543ec0b9054585a" }, "warehouse_id" : 2, "warehouse_name" : "Laptop", "warehouse_created" : { "$date" : 1447229590000 } }
(2 rows)

Accessing nested fields

MongoDB Foreign Data Wrapper allows you to access individual fields within nested JSON documents by mapping the nested structure to columns in a foreign table. This works by mapping the nested structure of the MongoDB document to relational columns in the foreign table definition, using dot notation (key2.subkey21) to reference nested fields. You can retrieve these fields from a collection as shown in the following example:

Example

db1> db.test_sub_json.find()
[
  {
    _id: ObjectId('658040214890799d6e0173d0'),
    key1: 'hello',
    key2: {
      subkey21: 'hello-sub1',
      subkey22: 'hello-sub2',
      subtstmp: ISODate('2022-12-16T19:16:17.801Z')
    }
  }
]

Steps for retrieving sub-fields from the document:

Create a foreign table. To access a sub-field use the dot (".") in the column name as shown below:

CREATE FOREIGN TABLE ft_nested_json_test(
    _id NAME,
    key1 varchar,
    "key2.subkey21"   varchar,
    "key2.subkey22"   varchar,
    "key2.subtstmp"   timestamp
)SERVER mongo_server
OPTIONS (database 'db1', collection 'test_sub_json');

Retrieve the document with sub-fields:

SELECT * FROM ft_nested_json_test;

Output

           _id            | key1  | key2.subkey21 | key2.subkey22 |     key2.subtstmp      
--------------------------+-------+---------------+---------------+------------------------
 658040214890799d6e0173d0 | hello | hello-sub1    | hello-sub2    | 16-DEC-22 19:16:17.801

Retrieve an individual field:

SELECT "key2.subkey21" FROM ft_nested_json_test;

Output

 key2.subkey21 
---------------
 hello-sub1

← Prev

Architecture overview

↑ Up

MongoDB Foreign Data Wrapper