TL;DR:
Joining data across SQL, NoSQL (MongoDB, Elasticsearch, Opensearch etc.), REST APIs, and other sources can be a complex engineering challenge. Knowi makes it easy with native multi-source joins, no ETL needed.
In this guide, we’ll walk through the process of joining MongoDB data with MySQL, Elasticsearch, REST APIs, and Amazon Redshift. Whether you’re a data analyst or a developer, you will learn how to build powerful cross-source analytics without writing complex backend code.
Introduction: Why Multi-Source Joins Matter
Data at company level is generally stored in multiple databases—think MongoDB for user activity, MySQL for customer profiles, and APIs for billing or IoT data.
Stitching these datasets together requires ETL pipelines, complex scripts, or data warehouses—taking a lot of time and effort and delaying decision making.
Enter Knowi.
With native support for SQL (MySQL, Postgres etc.), NoSQL(MongoDB, Elasticsearch, InfluxDB, CouchDB, Cassandra etc.), and REST API data sources, Knowi allows joins across these data systems without any need to move your data. You can just blend data from anywhere with just a few clicks or a simple config file.
Joining MongoDB data with MySQL Data
Let’s walk through a real-world example: joining MongoDB and MySQL to combine user activity with customer address data.
Step-by-Step MongoDB – My SQL Join Setup in Knowi
This is an example to join MongoDB to MySQl database. You can join any data including Elasticsearch, Rest APIs and Amazon Redshift in exactly the same way.
- Connect Your Data Sources
- Go to Queries in the Knowi interface.
- Click on New Datasource.
- From the list of available data sources, select MongoDB.
- Enter the required connection details for your MongoDB database.
- Click Test Connection to verify if the connection is successful.
- Once verified, click Save to add the MongoDB datasource.
- Repeat the same steps to set up your MySQL datasource.
2. Build the First Query to Join with MySQL (or Elasticsearch, REST APIs, or Amazon Redshift)
- Start a new query to join data from MongoDB and MySQL.
- Select MongoDB as the primary data source.
- Choose the fields you want to include in the dataset.
- Make sure to include the common key/field (e.g., customer_id) that exists in both MongoDB and MySQL to enable the join.
- Click Preview to see how your data looks.
3. Start a new query to join data from MongoDB and MySQL.
- Select MongoDB as the primary data source.
- Chose the fields you want to include in the dataset.
- Make sure to include the common key/field (e.g., customer_id) that exists in both MongoDB and MySQL to enable the join.
- Click Preview to see how your data looks.
4. Join MongoDB with MySQL (or Elasticsearch, REST APIs, or Amazon Redshift)
- Click on Join Builder.
- A prompt will appear to add a secondary data source.
- Choose the data source you want to join with—e.g., MySQL, Elasticsearch, REST API, or Amazon Redshift.
- For this example, select MySQL and pick the table you want to join (e.g., customer).
- Click Join Builder again to:
- Select the type of join: INNER, LEFT OUTER, FULL OUTER, etc.
- Choose the fields to join on (e.g., customer = customer).
5. Finalize the Join
- Click Preview to review the unified dataset.
- Once satisfied, click Save to use the joined dataset in: Dashboards, reports and alerts.
5. Choose Your Data Strategy
Once your query is ready, you’ll define how it behaves:
- Direct Query: Executes live on each load (with optional caching)
- Run Once: Executes on demand and stores the result in Knowi’s warehouse
- Custom Store: Saves the output back into MongoDB
- Scheduled Intervals: Run your query automatically on a schedule
You can also skip a query if a connected query is already running—useful for managing load.
- Once done – Name the dataset and same
7. Creating Dashboards with Knowi’s AI or Manually
Your query output is now a reusable dataset.
- Use Knowi’s AI to automatically generate dashboards with suggested insights.
- Cherry-pick insights you want to explore further or build upon.
- Or choose to manually create a dashboard:
- Click Add Dashboard and give it a name.
- Add a widget based on your query or dataset.
- Click Add Dashboard and give it a name.
- Click Analyze to:
- View the underlying data.
- Select relevant columns.
- Add functions to existing fields (e.g., aggregations, calculations).
- View the underlying data.
- Switch to the Visualization tab to choose your chart type and customize it.
- Click Save—your visualization will now appear on the dashboard.
- Tap the light bulb icon to get AI-powered instant insights from your data.
Advanced: Using the Cloud9Agent for In-Network Joins
For sensitive environments or large-scale joins, Knowi supports agent-based joins using the Cloud9Agent, which runs inside your network.
Define your joins with simple JSON configs:
“joinQueries”: [
{
“dsName”: “demoMongo”,
“joinKeys”: { “date”: “date” }
}
]
This is perfect for complex deployments or enterprises with strict data residency requirements.
Join Types Explained
Knowi supports all major join types:
- INNER JOIN (default)
- LEFT OUTER JOIN
- FULL OUTER JOIN
- RIGHT OUTER JOIN
- LOOP JOIN – Executes the second query row-by-row using dynamic tokens, ideal for API-based data sources.
When no join key is specified, Knowi automatically blends data across datasets.
Joining on Large Datasets
Knowi is architecturally designed to cater large scale joins across millions of records over disparate sources.
Joins are performed on the results of the two queries, where the join is performed in memory within Knowi, with a disk swap for larger datasets (with multi-threading and partitioning where applicable).
Join Optimization Hints (Examples):
| Hint | Description | Example |
| SORTED | Both datasets pre-sorted | SORTED INNER JOIN a = b |
| HASH LEFT | Left dataset hashed | HASH LEFT INNER JOIN a = b |
| MULTI HASH RIGHT | Right dataset hashed with multi-threading | MULTI HASH RIGHT INNER JOIN a = b |
To enable multi-threaded joins on-prem, update:
queryJoinThreads=10
No ETL. Real AI. True Flexibility.
What makes Knowi’s join capabilities truly stand out? It’s not just that you can connect different data sources—it’s how effortlessly and securely you can blend them together, all without writing complex ETL pipelines.
SQL + NoSQL + APIs: Join Anything, Anywhere
Knowi uniquely supports native joins across traditionally siloed data types. Whether your data lives in MongoDB, MySQL, Elasticsearch, or comes from a REST API, you can stitch it together easily. No need to move data or stage it in a data warehouse—Knowi connects directly and joins it on the fly.
Visual or Code-Based Workflow
Whether you’re a business analyst or a developer, Knowi makes analytics easy for you:
- Use the visual join builder to select fields and configure joins with just a few clicks.
- Prefer code? Use JSON-based configs through Cloud9Agent to define complex joins, filters, and transformations as code—ideal for automated pipelines or CI/CD workflows.
Secure by Design
Worried about data privacy or regulatory compliance? Knowi’s joins can execute entirely within your own network. With on-prem deployments or hybrid cloud setups, your data never has to leave your secure environment—even when using advanced AI features.
AI-Ready for instant insights
Once your data is joined, you’re not just stuck with a static table. You can immediately:
- Detect anomalies across joined datasets
- Generate dashboards automatically based on joined metrics and dimensions
- Use Knowi’s conversational interface – Lumo to talk to your data and get insights.
No additional prep, no context loss—Knowi’s AI understands the structure of your joined data and starts delivering insights right away.
Final Thoughts
Knowi makes it easy to unify your MongoDB data with other critical sources—like MySQL, REST APIs, Elasticsearch, and Redshift—without needing to build complex pipelines or move your data.
Whether you’re analyzing customer behavior, enriching product data, or powering dashboards across teams, Knowi gives you the speed, flexibility, and AI-driven insights you need to move faster.
Check out the data sources Knowi natively integrates with.
If your data source isn’t listed, let us know—we likely support it through REST APIs or custom connectors.
Request a demo to see how Knowi can simplify MongoDB data management and analytics for you.
Frequently Asked Questions
Can I join NoSQL with SQL?
Yes, Knowi supports joins across SQL, NoSQL, and REST-based sources.
What happens if join fields don’t match?
Data is blended instead of joined—ensuring flexibility without failure.
Is agent-based joining required?
Not unless you’re working with on-prem data or very large datasets.
Can I use these joins in dashboards?
Absolutely. Joined datasets are fully reusable in dashboards, alerts, and AI workflows.
