DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • HTAP Using a Star Query on MongoDB Atlas Search Index
  • Java and MongoDB Integration: A CRUD Tutorial [Video Tutorial]
  • Mixing SQL and NoSQL With MariaDB and MongoDB
  • MongoDB to Couchbase for Developers, Part 1: Architecture

Trending

  • Run Scalable Python Workloads With Modal
  • How to Troubleshoot Common Linux VPS Issues: CPU, Memory, Disk Usage
  • Multiple Stakeholder Management in Software Engineering
  • The Evolution of Software Integration: How MCP Is Reshaping AI Development Beyond Traditional APIs
  1. DZone
  2. Data Engineering
  3. Databases
  4. 3 Approaches to Creating a SQL-Join Equivalent in MongoDB

3 Approaches to Creating a SQL-Join Equivalent in MongoDB

Integrating MongoDB document data with SQL and other table-centric data sources needn't be so processor-intensive. Here's how.

By 
Darren Perucci user avatar
Darren Perucci
·
Sep. 18, 17 · Opinion
Likes (4)
Comment
Save
Tweet
Share
40.2K Views

Join the DZone community and get the full member experience.

Join For Free

While there's no such operation as a SQL-style table join in MongoDB, you can achieve the same effect without relying on table schema. Here are three techniques for combining data stored in MongoDB document collections with minimal query-processing horsepower required.

The signature relational-database operation is the table join: Combine data from table 1 with data from table 2 to create table 3. The schemaless document-container structure of MongoDB and other non-relational databases makes such table joins impossible.

Instead, as the MongoDB Manual explains, MongoDB either denormalizes the data by storing related items in a single document, or it relates that data in separate documents. One way to relate documents is via manual references — the _id field of one document is saved in the other document as a reference. The application simply runs the second query to return the related data.

When you need to link multiple documents in multiple collections, DBRefs let you relate documents using the value of one document's _id field, collection name, and, optionally, its database name. The application resolves DBRefs by running additional queries to return the referenced documents.

A tutorial in the MongoDB Manual demonstrates the use of denormalization in a social-media application. The manual also provides a SQL-to-aggregation mapping chart.

Simple Function for Joining Data Within a Single MongoDB Collection

An alternative approach to relating data in a MongoDB collection is via a function that you run in the MongoDB client console. The process is explained in a Stack Overflow post from March 2014.

For example, in a library database, you first create fields for authors, categories, books, and lending:

Then, you apply the function:

The result is the rough equivalent of a join operation on SQL tables.

Ensuring MongoDB Apps Integrate With Other Data

The lack of a one-to-one join equivalent is only one of the many ways MongoDB differs from SQL databases. In this post, Julian Hyde, lead developer of the Mondrian open-source OLAP engine, explains how he built a MongoDB-to-SQL interface using the Optiq dynamic data management framework.

Optiq features a SQL parser and a query optimizer powered by rewrite rules. Hyde created rules to map SQL tables onto MongoDB collections and to map relational operations onto MongoDB's find and aggregate operators. The result is the equivalent of a JDBC driver for MongoDB based on a hybrid query processing engine intended to shift as much query processing as possible to MongoDB. Joins and other operations are handled by the client.

The process allows you to convert each MongoDB collection to a table. The COLUMNS and TABLES system tables are supplied by Optiq, and the ZIPS view is defined in mongo-zips-model.json

MongoDB Relational database

Published at DZone with permission of Darren Perucci, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • HTAP Using a Star Query on MongoDB Atlas Search Index
  • Java and MongoDB Integration: A CRUD Tutorial [Video Tutorial]
  • Mixing SQL and NoSQL With MariaDB and MongoDB
  • MongoDB to Couchbase for Developers, Part 1: Architecture

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: