Join MongoDB with Any Database and Apply Conditional Filtering

Query Across MongoDB and Any Database, Without Writing Code

Modern applications often pair MongoDB with other databases, such as PostgreSQL, MySQL, SQL Server, or even another MongoDB instance, to meet different data modeling and performance needs.

While MongoDB offers flexibility through its document based collections, other SQL based databases provide structured, relational storage or specialized performance.

However, combining data across these systems, especially in real time, has traditionally been challenging. Developers often had to:

• Write custom backend logic to join and merge records
• Manage multiple API calls or aggregation pipelines
• Maintain fragile ETL workflows to keep data in sync

To solve this, API Maker introduces the Find & Join feature.

Why Use Find & Join for MongoDB and Any Other Database

Modern applications often follow a polyglot database architecture, using MongoDB for flexible, unstructured data alongside SQL or NoSQL databases like PostgreSQL, MySQL, SQL Server, or even another MongoDB instance for structured storage, analytics, or legacy integrations.

While this separation improves modularity and performance, it introduces a key challenge:

How do you fetch connected data across MongoDB and another database in a single query, without writing custom backend logic?

This is where API Maker’s Find & Join feature comes in.

It lets you perform deep, cross-database joins and apply conditional filtering between MongoDB and any supported database, all through simple REST APIs and declarative JSON queries.

This page focuses specifically on how to use the Find & Join feature with MongoDB as the source, joined with any other database, SQL or NoSQL. If you're looking for a general overview of how Find & Join works, check out the Find & Join Feature page.

Schema Setup in Api Maker

To enable Find & Join operations between MongoDB and any other supported database (SQL or NoSQL), you’ll need to define the relationship in your schema using API Maker’s Table Schema configuration.

Once the relationship is set up, API Maker automatically resolves cross-database joins at runtime, no backend code required, even when the databases use different engines.

For detailed guidance on schema definitions, refer to the Schema Setup Docs

Defining a Cross-Database Relationship in Schema

Let’s say:

• You have a profiles collection in MongoDB
• You want to join it with a users table or collection in another database (e.g., PostgreSQL, MySQL, SQL Server, or even another MongoDB instance).

You can define the relationship in your MongoDB schema of profiles collection like this:

user_id: <ISchemaProperty>{
    __type: EType.number,
    instance: "mysql_3306",
    database: "accounts_db",
    table: "users",
    column: "id"
}

Here’s what each property means:

• __type : The data type of the foreign key field (must match the target field's type).
• instance : The name of the target database instance configured in API Maker.
• database : The name of the database where the target table or collection exists.
• table: : The name of the table or collection you’re linking to.
• column : The field in the target table/collection you're referencing.

Once this relationship is set up:

• API Maker will automatically resolve the join at runtime
• You can query nested fields using dot notation (e.g., user_id.name)
• No backend logic is needed, it’s all handled through declarative JSON and REST API

This works whether you're joining MongoDB with SQL databases like PostgreSQL, MySQL, MariaDB or any other Supported Databases.

Example Scenarios

As we discussed:

• You have a profiles collection in MongoDB
• You have a users table in MySQL
• The field user_id in profiles references users.id in MySQL
• We have name field in users table

1. GET API Example : Use the find Query parameter in your auto-generated GET API to filter across relationships.

Request:

GET /api/profiles?find={ "user_id.name": "Alice" }

Response:

{
    "success": true,
    "statusCode": 200,
    "data": [
        {
            "_id": "64c8e7f1a8b3721f84cd9fae",
            "bio": "Tech enthusiast",
            "user_id": 42,
            "location": "San Francisco",
            "age": 28,
            "created_at": "2024-12-10T08:42:00Z"
        },
        {
            "_id": "64c8e7f1a8b3721f84cd9fb3",
            "bio": "Developer and open-source contributor",
            "user_id": 42,
            "location": "New York",
            "age": 31,
            "created_at": "2025-02-19T14:21:00Z"
        }
    ]
}

This query fetches all profiles (from MongoDB) where the related user (from MySQL) has the name "Alice".

2. POST API Example : POST APIs give you more flexibility, as we can pass details in request body.

Request:

POST /api/profiles/query
Content-Type: application/json

{
    "find": {
        "user_id.signup_date": {
            "$gt": "2024-01-01"
        },
        "user_id.role": "admin"
    },
    "limit": 3,
    "sort": {
        "user_id.signup_date": -1
    }
}

Response:

{
    "success": true,
    "statusCode": 200,
    "data": [
        {
            "_id": "64f9a1c4c8b3721f84cd912a",
            "bio": "Platform engineer",
            "user_id": 101,
            "location": "Chicago",
            "age": 34,
            "created_at": "2025-06-15T11:20:00Z"
        },
        {
            "_id": "64f9a1c4c8b3721f84cd912b",
            "bio": "Data analyst and trainer",
            "user_id": 84,
            "location": "Austin",
            "age": 29,
            "created_at": "2025-04-02T09:47:00Z"
        },
        {
            "_id": "64f9a1c4c8b3721f84cd912c",
            "bio": "Full-stack developer",
            "user_id": 58,
            "location": "Seattle",
            "age": 27,
            "created_at": "2025-02-20T18:10:00Z"
        }
    ]
}

This query returns up to 3 profiles from MongoDB linked to MySQL users who signed up after January 1st, 2024 and have the role "admin", sorted by the most recent signup date.

To fetch nested field values, you can use the deep object in your API calls. Refer to the Deep Populate page to learn more about retrieving values from related or nested records.

This same pattern works for MongoDB with any supported database, all through a unified JSON query, no backend coding required.

To explore all REST APIs that support Find & Join, check out the auto-generated REST APIs and schema-based REST APIs available on the API Maker Docs Page.

Comparison to Traditional Methods & Other Platforms

Joining data between MongoDB and other databases, like PostgreSQL, MySQL, MS SQL, or even another MongoDB instance, has traditionally been a complex, manual process for developers.

Traditional Methods: Before tools like API Maker, developers had to:

• Write custom backend logic to connect and query across databases
• Manually merge results at the application level
• Build and maintain fragile ETL pipelines
• Handle sync issues and performance bottlenecks

These methods are time consuming and hard to scale, especially when relationships become more complex.

How API Maker Simplifies Cross Database Joins and Conditional Filtering :

• No backend logic required
• Schema-based joins resolved automatically at runtime
• Real-time querying across different databases
• Simple REST API interface, easy to use from frontend or backend

Compared to Other Platforms

API Maker is the only platform among these that offers real-time, schema-aware, cross-database joins between MongoDB and any supported database, with no backend logic required.

FAQ's

1. Can I join MongoDB collections with PostgreSQL tables in both directions?

Yes. As long as the relationship is defined in your schema, you can perform joins in either direction, MongoDB → PostgreSQL or PostgreSQL → MongoDB, using the same query structure.

2. How do I filter on PostgreSQL fields from within a MongoDB collection?

You can use nested dot notation based on your schema definition.
For example: { "user_id.role": "admin" }.
In this case, user_id is a reference from the MongoDB profiles collection to the PostgreSQL users table having role field in it.

3. Is performance impacted by cross-database joins?

API Maker optimizes cross-database joins at runtime.
For best performance, make sure to index the join keys in both MongoDB and PostgreSQL.

4. Can I use Find & Join with databases other than PostgreSQL?

Yes. API Maker supports cross-database joins from MongoDB to any of the 8 supported databases, including MySQL, MariaDB, MSSQL, Oracle DB, PostgreSQL, TiDB, Percona XtraDB, and even another MongoDB instance, all using the same simple API structure.

5. Do I need to write custom backend logic for MongoDB cross joins?

No. Once your schema relationships are set, everything is handled at runtime.
You can query across databases using simple REST API calls, no custom backend needed.

6. What if my MongoDB data references multiple databases?

That’s fully supported. You can define multiple cross-database relationships in your schema, and API Maker will resolve joins dynamically, even if different fields in your MongoDB collection point to different databases.