Joining/merging unrelated collections in same aggregation?

Hello.
I need to build a search function in my backend and thinking how to do it. The search result will include results from 3 different collections that will not have anything related to use lookup or similar on.

Let say we have threee collections named movies, actors and series. And if I search for e.g tom in the frontend the search result should include the results from these three collections and should be displayed as separate results on the fronend e.g

(very simplified)
result = [{movies : [<Tomb Raider>]}, {actors: ['Tom Hanks], {series : [Tom]}},

Is this something I can do with just one aggregate/pipeline on one collection?

I’m leaning on just making three separate db aggregations for each collection and merging the result after the results are done, but thought i might ask about this first.

1 Like

Take a look at

and

Each $facet would $lookup in a different collection.

I have not tried that but I think it has a good potential.

1 Like

My first thought was using $facet with $bucket
but in my very limited understanding it only process the incoming documents from the stage above. So If i haven’t joined/looked up the three collections somehow in the prev stage the facet stage will only include the documents from the collection i run the pipeline on?

Or can I join somehow without related data in the $facet stage?

And i dont think i can use $lookup as i dont really have somthing to join on?

1 Like

I did not test that. I think you should try it and share your issues and findings so that we can all learn. You also need to share some sample documents so that we can help with any issues.

1 Like

Take a look at $unionWith, it allows a sub pipeline for $match.
Then collapse the 3 documents into a single document with $group.

3 Likes

Thanks @Dave_Lange, indeed $unionWith is the way to go.

See https://docs.mongodb.com/manual/reference/operator/aggregation/unionWith/ for examples.

2 Likes

You can use $unionWith or you can store all the documents in a single collection (with a field differentiating the type of document for querying). The best solution depends on what other reads and writes will you be making to these documents.

4 Likes

Thank you for your replies! $unionWith looks promising, but it turns out that our client wants to use fuzzy search and the most promising lib for this and that fits ours needs I found doesn’t support aggregation so i’ll just have to do regular .find() on each model an then join the result with regular JS later.

1 Like

Welcome @Dave_Lange !