Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

3 Approaches to Creating a SQL-Join Equivalent in MongoDB

DZone's Guide to

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.

· Database Zone
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,mongodb ,join ,sql

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

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}