[SERVER-25434] $lookup: allow option for FULL outer-join Created: 03/Aug/16  Updated: 06/Dec/22  Resolved: 19/Aug/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: tony kerz Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-23201 provide union operator to union two c... Closed
Assigned Teams:
Query
Participants:

 Description   

$lookup is currently analogous to a left-outer-join, in some cases, i have come across a requirement for a full-outer-join, and i think this could be a valuable enhancement.

see this stack-overflow question for additional background



 Comments   
Comment by Charlie Swanson [ 19/Aug/16 ]

Hi tony_k, it looks like the functionality you want has already been requested in SERVER-23201, so I'm closing this ticket as a duplicate of that. Please upvote SERVER-23201 and watch that ticket for future updates.

Comment by Ramon Fernandez Marina [ 03/Aug/16 ]

Thanks for your report tony-kerz. I'm copying the stack-overflow question below for easier reference and sending this to the query team for consideration.

Regards,
Ramón.

I have, what seems to be, a relatively simple task, to take data from two collections and "merge" them into a single collection. I have successfully been working with the aggregation framework and the $lookup operator for various other tasks.

I guess maybe the closest analogy i can come up with is a full outer join, but $lookup is really analogous to a left outer join.

As an example, let's say i have the following two collections:

collection1:

{key: 1, val: '1'}
{key: 2, val: '2'}

collection2:

{key: 3, val: '3'}
{key: 4, val: '4'}

and the desired end effect is:

collection3:

{key: 1, val: '1'}
{key: 2, val: '2'}
{key: 3, val: '3'}
{key: 4, val: '4'}

I know i can accomplish this "client-side", but there could be millions of records in each collection, and i was trying to get this done with as little data movement as possible for efficiency reasons.

My real requirement may also involve some grouping around matching records, such as can be accomplished via the $group operator, but i think that is an orthogonal concern, so i'm leaving it aside for now.

does anyone with more experience than i, have any suggestions around this type of thing?

Generated at Thu Feb 08 04:09:11 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.