How to properly utilize $lookup with alternate join conditions?

[Ether (John Asmar)]

Greetings! I’m new to MongoDB but I’ve looked at the documentation for a lot of the aggregation related commands. I was wondering about a portion of an aggregatetion I am currently working with:

// name is a predefined variable for a part name

{
    $lookup: {
      from: "parts",
      let: {
        family: name,
        isParent: false,
      },
      pipeline: [
        {
          $match: {
            family: "$family",
            isParent: "$isParent",
          },
        },
      ],
      as: "products",
    },
  },

I’m just a little confused on how to properly configure this properly. Currently it adds the products array to my current aggregate function, but it is completely devoid of content.

[turivishal (Vishal Turi)]

Hey @Ether Welcome to MongoDB Community Forum,

Look at the instruction provided in the join-conditions-and-uncorrelated-sub-queries,

Optional. Specifies variables to use in the pipeline field stages. Use the variable expressions to access the fields from the documents input to the $lookup stage.

The pipeline cannot directly access the input document fields. Instead, first define the variables for the input document fields, and then reference the variables in the stages in the pipeline .

NOTE

To reference variables in pipeline stages, use the "$$<variable>" syntax.

The let variables can be accessed by the stages in the pipeline, including additional $lookup stages nested in the pipeline .

• A $match stage requires the use of an $expr operator to access the variables. $expr allows the use of aggregation expressions inside of the $match syntax.Without the use of the $expr operator, $match can refer to fields in a document but cannot access variables defined by a $lookup let clause.The $expr operator only uses indexes on the from collection for equality matches. Non-equality match queries, such as range queries, cannot use indexes on the from collection.
• Other (non- $match ) stages in the pipeline do not require an $expr operator to access the variables.

---

But in your case you are passing external inputs not from references, so no need to use let here, you can add direct condition in your $match stage,

  {
    $lookup: {
      from: "parts",
      pipeline: [
        {
          $match: {
            family: name,
            isParent: false
          }
        }
      ]
      as: "products",
    }
  }

[Ether (John Asmar)]

Thank you for the reply, I was wondering about the external inputs. Could $lookup be used like this within the same collection? I have a master document that needs to embed an array of children documents, and they are all within the same collection. I applied your change to my query and it still returns an empty products array.

---

To be completely transparent, this is what I currently have:

   const series = await db
    .collection("part").aggregate([
      {
        $match: { family: name, isParent: true },
      },
      {
        $lookup: {
          from: "part",
          pipeline: [{ $match: { family: name, isParent: false } }],
          as: "products",
        },
      },
    ])

This returns

I can’t understand why the parent document isn’t populating with the children. name = ‘nvidia_3080’, so it should match for all the children document that aren’t parents and slot them into the products array…

[turivishal (Vishal Turi)]

@Ether

Could $lookup be used like this within the same collection?

Yes you can

and also there is a $graphLookup, stage, Performs a recursive search on a collection, with options for restricting the search by recursion depth and query filter.

https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/

---

@Ether

I applied your change to my query and it still returns an empty products array.

It would be more easy if post post some sample documents, one for parent and one for child.

[Ether (John Asmar)]

Understood, here are some sample documents:

child product:
{
_id: 136
type: “video_card”
family: “nvidia_3080”
isParent: false
}

parent product:
{
_id: 369
type: “video_card”
family: “nvidia_3080”
isParent: false
vendorList:[123,321]
}

[turivishal (Vishal Turi)]

@Ether

parent product :
{
_id: 369
type: “video_card”
family: “nvidia_3080”
isParent: false
vendorList:[123,321]
}

Make sure isParent: false is true for parent document!

---

Sample Documents

    {
      _id: 136,
      type: "video_card",
      family: "nvidia_3080",
      isParent: false
    },
    {
      _id: 369,
      type: "video_card",
      family: "nvidia_3080",
      isParent: true,
      vendorList: [123, 321]
    }

Query with $lookup:

db.part.aggregate([
  {
    $match: {
      family: "nvidia_3080",
      isParent: true
    }
  },
  {
    $lookup: {
      from: "part",
      pipeline: [
        {
          $match: {
            family: "nvidia_3080",
            isParent: false
          }
        }
      ],
      as: "products"
    }
  }
])

Playground

[Ether (John Asmar)]

I understand everything about this, thank you very much. The isParent being false was a typo.

I only get an empty array returned currently on the aggregation resoluton. It must be a quirk with the Node.js MongoDB Driver API, I am looking further into it.

However, I will mark your answer as correct because in vanilla MongoDB that is indeed what I am looking for. I just can’t get it to work in Node.js.