MongoDB $lookup with multiple conditions-CodePudding

I have two collections in MongoDB, items and categories.
items is

{
  _id: "some_id",
  category_A: "foo",
  category_B: "bar",
}

and categories is

{
  _id: "foo_id",
  name: "foo",
  type: "A"  
},
{
  _id: "bar_id",
  name: "bar",
  type: "B"  
}

I'm trying to use a pipeline to get foo_id and bar_id by using $lookup, but I don't understand why the category_A_out array always returns empty.
Here is the relevant step of the pipeline for category_A:

{
  from: 'categories',
  "let": {
    "category": "$name",
    "type": "$type"
  },
  "pipeline": [{
    "$match": {
      $expr: {
        $and: [
          { $eq: ["$category_A", "$$category"] },
          { $eq: ["$$type", "A"] }
        ]
      }
    }
  }],
  as: 'category_A_out'
}

I am sure that foo and bar exist in the categories collection.

What am I doing wrong?

CodePudding user response：

let should use for declaring the variable for LEFT collection which is items.

If category_A holds the categories' name, you need match with name.

Else match with _id.

db.items.aggregate([
  {
    $lookup: {
      from: "categories",
      "let": {
        "category_A": "$category_A"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$name",    // Or Match with $_id if category_A holds id
                    "$$category_A"
                  ]
                },
                {
                  $eq: [
                    "$type",
                    "A"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "category_A_out"
    }
  }
])

Sample Mongo Playground