3 Approaches to Creating a SQL-Join Equivalent in MongoDB

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