MongoDB Deep Populate: Simplifying Cross-Database Queries

What is MongoDB Deep Populate?

API Maker's MongoDB Deep Populate revolutionizes how developers manage complex data relationships across multiple database systems. Unlike MongoDB's native $lookup or Mongoose's populate, which are limited to a single MongoDB instance, Deep Populate enables seamless querying across MongoDB and relational databases like MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, TiDB, and Percona XtraDB in a single API call. This delivers unified, structured responses, making it ideal for dashboards, microservices, and enterprise applications.

Deep Populate extends MongoDB’s querying capabilities by fetching related data from different database engines, combining MongoDB’s flexible document structure with the structured reliability of SQL databases. This eliminates the need for complex backend orchestration, delivering nested data in a single, elegant API response.

Quick Start: Setting Up Deep Populate

Requirements

• API Maker project configured
• MongoDB instance connected
• Related database instances (SQL or NoSQL)
• Defined schemas (recommended for optimal performance)

Schema Creation for Deep Populate

A well-defined schema in API Maker organizes data and enhances Deep Populate efficiency by mapping relationships across databases without additional JSON wrappers.

Example Schema (MongoDB products Collection)

{
  "collection": "products",
  "fields": {
    "_id": { "type": "objectId", "validations": { "required": true } },
    "name": { "type": "string", "validations": { "required": true } },
    "price": { "type": "number" },
    "category": { "type": "string" },
    "manufacturer_id": {
      "type": "string",
      "instance": "mysql",
      "database": "inventory",
      "collection": "manufacturers",
      "column": "id"
    }
  }
}

Why It Works

The manufacturer_id field embeds join configuration (instance, database, collection, column), enabling seamless cross-engine joins. API Maker automatically handles data type conversions (e.g., ObjectId to integer) when querying via /api/schema/mongo/.... See the official schema documentation for details.

Using Deep Populate with MongoDB

Deep Populate supports various HTTP methods to fetch or update data across databases with MongoDB as the core.

GET Request Example

Fetch products and their latest purchase records from a MySQL database:

Request

GET /api/schema/mongo/shop/products?find={"status":"active"}&deep=[{
  "s_key": "id",
  "t_instance": "mysql",
  "t_db": "store",
  "t_col": "purchases",
  "t_key": "product_id",
  "isMultiple": true,
  "select": "order_id,quantity,status",
  "find": {"status": "completed"},
  "limit": 5
}]

Response

{
  "name": "Smartwatch",
  "category": "Wearables",
  "purchases": [
    { "order_id": 321, "quantity": 2, "status": "completed" },
    { "order_id": 322, "quantity": 1, "status": "completed" }
  ]
}

Post Request Example

Update products with nested purchase data:

Request

PUT /api/schema/mongo/shop/products
Content-Type: application/json

{
  "id": "abc123",
  "name": "Smartwatch",
  "deep": [
    {
      "s_key": "id",
      "t_instance": "mysql",
      "t_db": "store",
      "t_col": "purchases",
      "t_key": "product_id",
      "find": { "status": "completed" },
      "select": "order_id,status",
      "isMultiple": true,
      "limit": 3
    }
  ]
}

Response

{
  "status": "updated",
  "product": {
    "id": "abc123",
    "name": "Smartwatch",
    "purchases": [
      { "order_id": 2001, "status": "completed" },
      { "order_id": 2002, "status": "completed" }
    ]
  }
}

This same format can be used to fetch data from any database within API Maker with MongoDB as the main database.

Core Fields in Deep Populate

See Core Fields Documentation.

Single-Level vs. Multi-Level Deep Populate

Single-Level Deep Populate

Best for fetching one related entity per document.

Request Example

GET /api/schema/mongo/shop/cities?find={"active":true}&deep=[
  {
    "s_key":"state_id",
    "t_instance":"oracle",
    "t_db":"geo",
    "t_col":"states",
    "t_key":"id",
    "find":{"state_name":"GUJARAT"},
    "isMultiple":false,
    "select":"state_name,id",
    "limit":5
  }
]

Response

[
  {
    "id": "64f1a8ef92",
    "city_name": "AHMEDABAD",
    "state_id": {
      "id": 2101,
      "state_name": "GUJARAT"
    }
  },
  {
    "id": "64f1a8ef93",
    "city_name": "SURAT",
    "state_id": {
      "id": 2101,
      "state_name": "GUJARAT"
    }
  }
]

Details

• Source: cities collection (MongoDB).
• Goal: Fetch each city’s state from an Oracle database.
• How: The deep array links s_key to t_key.
• Optimization: Use select and limit for faster responses.

Multi-Level Deep Populate

Ideal for hierarchical relationships (e.g., City → State → Country).

Request Example

GET /api/schema/mongo/shop/cities?find={"active":true}&deep=[
  {
    "s_key":"state_id",
    "t_instance":"oracle",
    "t_db":"geo",
    "t_col":"states",
    "t_key":"id",
    "select":"state_name,country_id",
    "isMultiple":false,
    "deep":[
      {
        "s_key":"country_id",
        "t_instance":"mysql",
        "t_db":"inventory",
        "t_col":"countries",
        "t_key":"id",
        "select":"country_name",
        "isMultiple":false
      }
    ]
  }
]

Response

{
  "id": 101,
  "city_name": "AHMEDABAD",
  "state_id": {
    "id": 201,
    "state_name": "GUJARAT",
    "country_id": {
      "id": 301,
      "country_name": "INDIA"
    }
  }
}

Details

• Chained Joins: Nested deep arrays define each join level.
• Cross-Engine: Links MongoDB (cities) to Oracle (states) to MySQL (countries).
• Flexibility: Supports unlimited nesting, though 3–4 levels are recommended for performance.

Summary

Supported Databases

Deep Populate integrates MongoDB with:

• MySQL
• PostgreSQL
• Oracle
• SQL Server
• MariaDB
• TiDB
• Percona XtraDB

Use these interchangeably in t_instance.

Benefits of Pairing MongoDB with Relational Databases

Pairing MongoDB’s flexible document model with relational databases creates a powerful, scalable data ecosystem. MongoDB excels at dynamic, unstructured data, while relational databases ensure structured, transactional reliability.

• MySQL / MariaDB: Handles orders, inventory, or sessions with fast transactions, while MongoDB manages flexible product catalogs or event logs. Ideal for e-commerce or user systems.
• PostgreSQL: Offers robust querying and indexing for analytics, blending structured data with MongoDB’s documents for real-time insights.
• Oracle: Supports enterprise auditing and compliance, linking to MongoDB’s adaptable storage for scalable solutions in finance or healthcare.
• SQL Server: Integrates with Microsoft systems for CRM or billing, paired with MongoDB for dynamic data flexibility.
• TiDB: Scales globally for finance or telemetry, complementing MongoDB’s schema-less design for high-performance apps.
• Percona XtraDB: Optimized for logging and analytics, paired with MongoDB for efficient, real-time data processing.
• MongoDB (Base): Enables rapid development with flexible schemas and high write speed, perfect for content management, IoT, or real-time apps.

This combination supports fast, adaptable applications for analytics, compliance, and more.

Database Capability Comparison

Best Practices

• Index s_key and t_key for faster joins.
• Use select to reduce payload size.
• Apply limit and sort for paginated nested data.
• Enable caching for static cross-database lookups.

FAQs

Can I use Deep Populate without schemas?

Yes, via /api/gen/..., but schemas improve performance and validation.

What if a target database is offline?

API Maker returns primary documents with null or empty nested fields, with errors in the errors field.

How many nesting levels are supported?

No strict limit, but 3–4 levels are optimal for performance and readability.

Does Deep Populate support ObjectId joins?

Yes, API Maker automatically converts ObjectId for SQL or other types if defined in the schema.

Can I paginate nested results?

Yes, use limit and skip in the deep object for pagination, especially with isMultiple: true.

How does Deep Populate differ from MongoDB’s $lookup?

$lookup is limited to MongoDB; Deep Populate works across SQL and NoSQL databases.

Does Deep Populate support streaming APIs?

Yes, API Maker supports streaming for large isMultiple datasets.

Is caching enabled?

Yes, configurable at the API or collection level for better performance.

How can I test Deep Populate requests?

Use the API Maker Test Console, Postman, cURL, or JavaScript (fetch/axios).

Learn More

• API Maker Deep Populate
• Schema Creation
• Schema API Reference
• API Examples

MongoDB Deep Populate unifies your data stack, delivering documents and relational data in one efficient response.