How to join data from SQL and NoSQL databases

To join SQL and NoSQL data, organizations use ETL pipelines, application-level code, or data virtualization platforms like Presto, Trino, or Knowi. Virtualization offers the most direct path by querying sources in place and joining data at query time without data movement.

TL;DR: How to Join SQL and NoSQL Data

• The Core Problem: SQL’s structured, relational model clashes with NoSQL’s flexible, often nested schema, making direct joins impossible at the database level.
• Traditional Method (ETL): Extract, Transform, and Load data from all sources into a central data warehouse. This approach is slow, expensive, and creates data latency.
• Application-Level Joins: Write custom code to fetch data from each source and merge the results in your application’s memory. This method is not scalable and creates performance bottlenecks.
• Data Virtualization (No-ETL): Use a federated query engine or analytics layer to connect to sources live. This allows you to execute a single query that joins data across databases without moving it.
• Key Challenge (Nested Data): NoSQL’s nested JSON arrays must be handled. Traditional tools require flattening this data, which leads to data loss, while modern platforms can process it natively.
• The Goal: Achieve a unified view of all data for real-time analytics, reducing reliance on brittle data pipelines and engineering overhead.

Table of Contents

• Why Traditional Joins Between SQL and NoSQL Fail
• The No-ETL Alternative: Data Virtualization and Federation
• Worked Example: How to Join MongoDB and PostgreSQL Data
• Worked Example: How to Unify API, SQL, and NoSQL Data
• Choosing Your Integration Strategy

Why Traditional Joins Between SQL and NoSQL Fail

Modern data architectures are inherently hybrid, blending the transactional integrity of SQL databases like PostgreSQL with the scale and flexibility of NoSQL systems like MongoDB. While this approach optimizes for application performance, it creates significant challenges for analytics. Joining data across these structurally different systems requires overcoming fundamental architectural divides.

The Schema Mismatch Problem

Relational databases enforce a schema-on-write model. Data structure is rigidly defined before any data is written, ensuring consistency and integrity through tables, columns, and explicit relationships. This structure is what makes traditional SQL JOIN operations efficient.

In contrast, most NoSQL databases use a schema-on-read model. Data, often in JSON format, can be stored without a predefined structure, offering immense flexibility for developers. However, this lack of a consistent schema makes it impossible for a SQL database to directly query and join with a NoSQL collection.

The Challenge of Nested JSON

A primary difficulty in cross-database joins is handling nested JSON documents common in NoSQL. A single MongoDB document might contain an array of objects, such as a customer record with a list of all past orders. A PostgreSQL table would represent this as separate rows in a separate orders table, linked by a foreign key.

To join these, traditional ETL processes must flatten the nested JSON into a relational format. This process is complex, brittle, and often results in a loss of data granularity or context. Any change to the nested structure in the source NoSQL database can break the entire pipeline.

ETL Fragility and Data Latency

The most common solution for cross-source analytics has been the ETL pipeline. This process involves extracting data from both SQL and NoSQL sources, transforming it into a unified format, and loading it into a central data warehouse. While functional, this model is fraught with issues for modern analytics.

ETL pipelines are brittle; a minor schema change in a source system can cause the entire pipeline to fail, requiring constant maintenance from data engineering teams. Furthermore, because ETL runs in batches, the data in the warehouse is never truly real-time. This latency can render insights obsolete for operational use cases that demand up-to-the-second information.

The No-ETL Alternative: Data Virtualization and Federation

A modern approach to cross-database joins without ETL is data virtualization, also known as data federation. This technique creates a unified, virtual data layer that connects directly to disparate sources in real time. Instead of moving data, it queries each system in place and joins the results in memory.

A data virtualization engine acts as an intelligent query broker. When a user executes a query to join a SQL table with a NoSQL collection, the engine translates that query into the native language of each source system. It then pushes down as much of the processing as possible to the source databases to optimize performance.

This method eliminates the cost and complexity of building and maintaining ETL pipelines and data warehouses. According to a 2022 Gartner report on data integration, data virtualization is a key component of a modern data fabric architecture, enabling more agile and flexible data access. It provides a single point of access for analytics while leaving the source data undisturbed.

Worked Example: How to Join MongoDB and PostgreSQL Data

Let’s consider a common scenario: joining user data from a PostgreSQL users table with user activity logs stored in a MongoDB events collection. The goal is to analyze which user actions correlate with subscription tiers. The common key between them is user_id.

Step 1: Identify the Common Join Key

First, identify the field that links the two datasets. In our PostgreSQL users table, this is the primary key id (an integer). In the MongoDB events collection, this is the user_id field (a string).

A capable data federation layer can handle the type casting between the integer and string formats during the join operation. It is critical that the user_id field in MongoDB is indexed to prevent slow collection scans during the join lookup.

Step 2: Configure Source Connections

Within your analytics platform, you must configure connections to both data sources. The PostgreSQL connection typically uses a standard JDBC driver, requiring the host, port, database name, and credentials. The MongoDB connection uses a native connector, which is more efficient at handling the MongoDB wire protocol and its specific features like nested data.

Secure network access, such as VPC peering or firewall rules, must be in place to allow the analytics platform to reach both databases. This connectivity forms the foundation for the virtual join.

Step 3: Execute the Federated Query

With the connections established, you can execute a single query that defines the join logic. While the syntax varies between platforms, the conceptual query would look something like this:

SELECT u.name, u.subscription_tier, e.event_name, e.timestamp FROM postgresql.users u JOIN mongodb.events e ON u.id = e.user_id WHERE u.signup_date > ‘2026-01-01’

The federation engine parses this query, sends a SELECT name, subscription_tier, id FROM users WHERE signup_date > '2026-01-01' query to PostgreSQL, and for each returned id, it sends a db.events.find({user_id: "..."}) query to MongoDB. The results are then joined in memory and returned to the user, providing a unified view without data duplication. The same principles can be used to join MongoDB with MySQL, Elasticsearch, REST and Redshift.

Worked Example: How to Unify API, SQL, and NoSQL Data

Expanding on the previous example, imagine you also need to enrich user data with information from a third-party REST API, such as Clearbit for company firmographics. This requires a three-way join between PostgreSQL, MongoDB, and an external API to answer questions like, “What is the industry breakdown of users who performed the ‘Trial Started’ event?”

Step 1: Configure All Data Sources

In addition to the PostgreSQL and MongoDB connections, you must configure the REST API as a data source. This involves specifying the API endpoint, the authentication method (e.g., API Key in the header), and any necessary parameters. The analytics platform should be able to treat the JSON response from the API as a queryable data source.

Step 2: Define the Multi-Source Join Logic

The join now involves multiple steps. First, join the PostgreSQL users table with the MongoDB events collection on user_id as before. Then, take the email field from the PostgreSQL results and use it to query the Clearbit API.

A unified analytics platform can chain these operations. The query logic effectively says: “For all users who started a trial, get their email from PostgreSQL, then call the Clearbit API with that email to get their company’s industry.” This technique is also powerful when you need to join data from two REST APIs together.

Step 3: Execute and Visualize the Unified Data

The final query joins the results from all three sources into a single, virtual dataset. This blended data can then be used to build visualizations, such as a pie chart showing the industry breakdown of trial users, all without landing any data in a warehouse. This approach is highly effective for a wide range of use cases in modern NoSQL analytics.

Similarly, these methods can be adapted for other combinations, such as enabling Elasticsearch + MySQL joins without ETL or creating virtual datasets to join Elasticsearch with other datasources.

Choosing Your Integration Strategy

Selecting the right method to join SQL and NoSQL data depends on your specific requirements for data freshness, engineering resources, and query complexity. Each approach involves different trade-offs in performance, cost, and flexibility. The table below compares the most common strategies.

Comparison of Integration Methods

When to Use Each Approach

For large-scale, historical business intelligence where data latency is acceptable, a traditional data warehouse with ETL remains a viable option. It is optimized for complex analytical queries over massive, structured datasets. However, it is not suitable for operational analytics on live data.

Application-level joins should only be used for simple, low-volume tasks. Attempting to use this method at scale will inevitably lead to performance degradation and unmanageable code complexity. It is not a sustainable strategy for enterprise analytics.

Data federation engines like Presto and Trino are powerful for data engineering teams that need to run ad-hoc SQL queries across diverse sources. They require significant technical expertise to set up, manage, and tune for performance. They are an infrastructure layer, not a full-featured analytics platform.

See it on your own data. Knowi joins MongoDB, PostgreSQL, Elasticsearch, and your APIs in a single query, no ETL, no warehouse. Request a demo at knowi.com.

Where a Unified Analytics Platform Fits

A unified analytics platform is designed to provide a complete, end-to-end solution for multi-source data access and visualization without ETL. As of 2026, Knowi appears to be one of the few platforms that natively joins SQL, NoSQL, and API data in a single virtual layer. It is built to handle the complexity of modern data ecosystems out of the box.

This approach is ideal for organizations that need real-time analytics on live, operational data, especially when dealing with unmodeled nested JSON from sources like MongoDB or Elasticsearch. It also reduces the burden on data engineering teams by allowing analysts to perform cross-database joins directly. For example, it can be used to join Couchbase and SQL data for immediate insights.

However, if your organization is already fully invested in a single-vendor ecosystem like Snowflake and has a large team dedicated to managing ETL, a traditional BI tool may be sufficient. For purely relational workloads without NoSQL or API data, standard SQL-based tools are highly efficient.

Frequently Asked Questions

Can you join SQL and NoSQL in a single query?

Yes, but not directly at the database level. You need a middle layer, such as a data virtualization or federated analytics platform, that can connect to both sources, translate a single query into the native language of each database, and join the results in memory.

What is the best tool for cross-database joins?

The best tool depends on the use case. For data engineers running ad-hoc queries, federation engines like Presto or Trino are powerful. For business users and embedded analytics requiring a full platform with visualization and NLQ, a unified analytics platform like Knowi is often a better fit.

How do I join MongoDB with PostgreSQL?

To join MongoDB and PostgreSQL, you use a federated query tool. You connect to both databases, identify a common key (like a user ID), and then write a single query that specifies the join condition. The tool handles fetching data from both systems and combining it.

Do I need a data warehouse to join different databases?

No. While a data warehouse is the traditional solution, modern data virtualization and No-ETL platforms allow you to join data from different databases at query time without moving the data to a central repository. This provides real-time access and reduces infrastructure costs.

Is it possible to join data from a REST API with a database?

Yes. Advanced analytics platforms can treat a REST API endpoint as a data source. You can execute a query against a database, use a value from the result (like an email address) to make a call to the API, and then join the API’s JSON response back to the original database results.

What are the performance risks of federated queries?

The primary performance risk is inefficient query execution, such as pulling too much data from source systems over the network. This is mitigated by modern federation engines that perform “pushdown” optimization, sending as much of the filtering and aggregation logic as possible to the source databases to process locally.

How can I use natural language to query joined data?

Some platforms support Natural Language Query (NLQ) on top of federated data sources. This allows non-technical users to ask questions in plain English, which are then translated into complex, multi-source queries by an AI query agent across SQL, NoSQL and API, returning a unified answer.

Request a demo at knowi.com.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [{
“@type”: “Question”,
“name”: “Can you join SQL and NoSQL in a single query?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Yes, but not directly at the database level. You need a middle layer, such as a data virtualization or federated analytics platform, that can connect to both sources, translate a single query into the native language of each database, and join the results in memory.”
}
},{
“@type”: “Question”,
“name”: “What is the best tool for cross-database joins?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The best tool depends on the use case. For data engineers running ad-hoc queries, federation engines like Presto or Trino are powerful. For business users and embedded analytics requiring a full platform with visualization and NLQ, a unified analytics platform like Knowi is often a better fit.”
}
},{
“@type”: “Question”,
“name”: “How do I join MongoDB with PostgreSQL?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “To join MongoDB and PostgreSQL, you use a federated query tool. You connect to both databases, identify a common key (like a user ID), and then write a single query that specifies the join condition. The tool handles fetching data from both systems and combining it.”
}
},{
“@type”: “Question”,
“name”: “Do I need a data warehouse to join different databases?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “No. While a data warehouse is the traditional solution, modern data virtualization and No-ETL platforms allow you to join data from different databases at query time without moving the data to a central repository. This provides real-time access and reduces infrastructure costs.”
}
},{
“@type”: “Question”,
“name”: “Is it possible to join data from a REST API with a database?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Yes. Advanced analytics platforms can treat a REST API endpoint as a data source. You can execute a query against a database, use a value from the result (like an email address) to make a call to the API, and then join the API’s JSON response back to the original database results.”
}
},{
“@type”: “Question”,
“name”: “What are the performance risks of federated queries?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The primary performance risk is inefficient query execution, such as pulling too much data from source systems over the network. This is mitigated by modern federation engines that perform “pushdown” optimization, sending as much of the filtering and aggregation logic as possible to the source databases to process locally.”
}
},{
“@type”: “Question”,
“name”: “How can I use natural language to query joined data?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Some platforms support Natural Language Query (NLQ) on top of federated data sources. This allows non-technical users to ask questions in plain English, which are then translated into complex, multi-source queries by an AI query agent across SQL, NoSQL and API, returning a unified answer.”
}
}]
}