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.
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.
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
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
TABLES system tables are supplied by Optiq, and the ZIPS view is defined in mongo-zips-model.json
Published at DZone with permission of Darren Perucci, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.