Скачать или смотреть How to Efficiently Query Multiple Concatenated Collections in MongoDB

Discover how to query multiple MongoDB collections simultaneously as if they were a single collection using the aggregation framework.
---
This video is based on the question https://stackoverflow.com/q/63805766/ asked by the user 'Masoudy BaBa' ( https://stackoverflow.com/u/4465330/ ) and on the answer https://stackoverflow.com/a/63808239/ provided by the user 'Kevin Smith' ( https://stackoverflow.com/u/4079967/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: MongoDB: querying against multiple concatenated collections

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

MongoDB is a powerful NoSQL database that allows users to store data in flexible, schema-less formats. However, when dealing with multiple collections that share the same schema, it can be challenging to query them seamlessly. Users often face situations where they want to treat separate collections as a single entity without the complexities of joining them. This is particularly useful for applications that need to access concurrent records across collections based on their insert time. In this post, we will explore how to efficiently query multiple concatenated collections in MongoDB using the aggregation framework.

The Problem

Imagine you have two collections, C1 and C2, containing documents of the same type. You want to query them simultaneously and retrieve the results sorted based on the time of insertion. The ideal output is to have documents from both collections interleaved, in the order they were inserted.

For instance, if C2 has documents C2_d1 and C2_d2, and C1 has documents C1_d1, C1_d2, and C1_d3, your goal is to retrieve them in this exact order:

C2_d1

C1_d1

C2_d2

C1_d2

C1_d3

Collection Structure Example

Here is an example of how the collections might look:

C1 Collection:

C1_d1

C1_d2

C1_d3

C2 Collection:

C2_d1

C2_d2

The Solution

To achieve a seamless query across multiple collections, you can utilize the $unionWith operator in MongoDB’s aggregation framework. This operator allows you to combine documents from different collections into a single output stream while maintaining their original structure.

Step 1: Use the $unionWith Operator

Insert the following aggregation command to merge your collections:

[[See Video to Reveal this Text or Code Snippet]]

This command will combine the documents from C1 and C2, keeping the original document structure intact.

Step 2: Sort the Combined Output

If you wish to sort the combined records based on a specific field, such as the insertion time, you can add a sort stage to the pipeline:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Create a View for Simplification

If you frequently perform this operation, it may be beneficial to create a view to simplify future queries. Use the following command to create a view called View1:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Insert Data into Your Collections

To illustrate, insert some sample data:

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Query the View and Retrieve Results

Once your view is prepared, you can query it just like you would a normal collection:

[[See Video to Reveal this Text or Code Snippet]]

This will return the documents in the desired interleaved order.

Accessing the View in Java

If you’re working in Java with MongoDB, you can access the view using the following code:

[[See Video to Reveal this Text or Code Snippet]]

Troubleshooting

If you encounter errors during view creation, such as:

[[See Video to Reveal this Text or Code Snippet]]

Make sure to enable the appropriate feature compatibility version with:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By leveraging the $unionWith operator in MongoDB’s aggregation framework, you can efficiently query multiple collections as if they are a single collection. This method not only streamlines the querying process but also ensures you can retrieve records in their insertion order. Whether you’re building a new application or optimizing existing queries, these techniques can greatly enhance your data handling capabilities. Happy querying!