[SERVER-22787] Add $lookup.excludeLocalField to exclude local field from output Created: 22/Feb/16  Updated: 06/Apr/23

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

Type: New Feature Priority: Minor - P4
Reporter: Jose Antonio Illescas Olmo Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: eng-s
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-18966 Allow exclusion in $project stage of ... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

{ $lookup: {
       from: <collection to join>,
       localField: <field from the input documents>,
       excludeLocalField: <boolean>
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
} }

If excludeLocalField = true then localField is removed from output
IMHO: excludeLocalField must be default to true (excludedd by default)



 Comments   
Comment by Dissatisfied Former User [ 13/Jun/16 ]

To date I have resolved this by simply having the $lookup use the same field name for both from and as keys. This replaces the reference with an array of objects matching the reference, often used with projection. As an example:

db.foo.aggregate([
    {"$group": {"_id": "$division", "latest": {"$first": "$invoice"}},
    {"$lookup": {"localField": "latest", "from": "Invoices", "foreignField": "_id", "as": "latest"}},
    {"$unwind": "$latest"}
])

The immediate unwind step is a fairly common pattern, too.

Comment by Jose Antonio Illescas Olmo [ 23/Apr/16 ]

A possible workaround for this: SERVER-18966 ("allow exclusion in $project stage")

Comment by Jose Antonio Illescas Olmo [ 24/Feb/16 ]

No, sorry for my poor explanation:
In your example:

db.foo.aggregate([
{$lookup: {
from: "bar",
localField: "bar_id",
foreignField: "_id",
excludeLocalField: true,
as: "bars"
}}
])
if the $lookup stage had included excludeLocalField: true (instead of false) then remove referenced localField ("bar_id" in your example)

Comment by Charlie Swanson [ 24/Feb/16 ]

Oh, I'm sorry. You want it to remove all local fields? Can you provide an example of what you mean?

Comment by Jose Antonio Illescas Olmo [ 24/Feb/16 ]

Yes, its correct (but I think that <excludeLocalField: true> must hide/remove local fields)

Comment by Charlie Swanson [ 23/Feb/16 ]

jalvarez_madrigal@hotmail.com, If I understand the request correctly, you're looking for this change in behavior:

Example Schema
test.foo test.bar

{_id: 0, bar_id: 1}
{_id: 1, bar_id: 42}

{_id: 1}
{_id: 42}

Example Pipeline

db.foo.aggregate([
  {$lookup: {
    from: "bar",
    localField: "bar_id",
    foreignField: "_id",
    as: "bars"
  }}
])

Example Results, current behavior

{_id: 0, bar_id: 1, bars: [{_id: 1}]}
{_id: 1, bar_id: 42, bars: [{_id: 42}]}

Example Results, desired behavior

{_id: 0, bars: [{_id: 1}]}
{_id: 1, bars: [{_id: 42}]}

If the $lookup stage had included excludeLocalField: false, then the output would match the current behavior? Can you confirm?

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