Having issues matching local and foreign fields in a $lookup pipeline for a MongoDB aggregate query-CodePudding

I have a two different collections, one for emailtemplates, and one for emails. And what I'm trying to do is write an aggregate pipeline that will show how many times each template has been sent using the templates name from the emailtemplates collection. I know I could just execute a pipeline on the emails collection and count each one by the template name, but that will exclude templates that have never been sent.

Here are some example documents I worked up...

// Example emailtemplate documents:
[
  {
    "name": "WELCOME-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, Welcome to the club"
  },
  {
    "name": "GOODBYE-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, were sorry to see you go"
  },
  {
    "name": "YOURE-FIRED-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, Pack your stuff and go"
  }
]


// Example email documents:
[
  {
    "templateName": "WELCOME-01",
    "recipient": "[email protected]",
    "parameters": {
      "firstName": "John",
      "lastName": "Doe"
    }
  },
  {
    "templateName": "WELCOME-01",
    "recipient": "[email protected]",
    "parameters": {
      "firstName": "David",
      "lastName": "Chappelle"
    },
  },
  {
    "templateName": "GOODBYE-01",
    "recipient": "[email protected]",
    "parameters": {
      "firstName": "The",
      "lastName": "Joker"
    }
  }
]

So you can see how each email document has the templateName value which matches with the name from each emailtemplates document. And what I'm trying to do is select from the templateName collection and show how many emails documents are associated to it.

I know how to do it using the localField and foreignFIeld options:

db.notificationtemplates.aggregate([
  {
      $lookup:{
        from: "notifications",
        localField: "name",
        foreignField: "templateName",
        as: "notifications"
      }
   },
   {
    $project:{
      _id: 0,
      templateName: "$name",
      amountSent: { $size: "$notifications"}
    }
   }
 ]);

Which gives me the results:

[
  { templateName: 'WELCOME-01', amountSent: 2 },
  { templateName: 'GOODBYE-01', amountSent: 1 },
  { templateName: 'YOURE-FIRED-01', amountSent: 0 }
}

And that works just fine, but I need to add some logic to the $lookup, which means I need a $pipeline in there, which means I can't simply use the localField and foreignField. Otherwise I get the error:

MongoServerError: $lookup with 'pipeline' may not specify 'localField' or 'foreignField'

Here's the query I've written thus far to try to do the same thing:

 db.emailtemplates.aggregate([
  { $match:{channel: 'email'} },
  {
    $lookup: {
      from: "emails",
      let: {
        templateName: "$templateName",
        name: "$name"
      },
      pipeline: [
        { $match: { $expr: {$eq: [ "$$templateName","$name"] } } },
        { $project:{
            "templateName":"$templateName",
            "name":"$name"
        } }
      ],
      as: "emails"
    }
  }
])

Here are the results of the query above:

[
  {
    "name": "WELCOME-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, Welcome to the club",
    "emails":[
      {
        "templateName": "WELCOME-01"
      },
      {
        "templateName": "WELCOME-01",
      },
      {
        "templateName": "GOODBYE-01"
      }
    ]
  },
  {
    "name": "GOODBYE-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, were sorry to see you go",
    "emails":[
      {
        "templateName": "WELCOME-01"
      },
      {
        "templateName": "WELCOME-01",
      },
      {
        "templateName": "GOODBYE-01"
      }
    ]
  },
  {
    "name": "YOURE-FIRED-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, Pack your stuff and go",
    "emails":[
      {
        "templateName": "WELCOME-01"
      },
      {
        "templateName": "WELCOME-01",
      },
      {
        "templateName": "GOODBYE-01"
      }
    ]
  }
]

Note: I'm only outputting the templateName now so I can see what documents get matched for the emails value.

If you look at the emails value for each document that's output, it doesn't at all only look for the emails with the templateName matching the local name value of the emailtemplate documents.

The output I'm expecting to see would be something more along the line of:

[
  {
    "name": "WELCOME-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, Welcome to the club",
    "emails":[
      {
        "templateName": "WELCOME-01"
      },
      {
        "templateName": "WELCOME-01"
      }
      }
    ]
  },
  {
    "name": "GOODBYE-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, were sorry to see you go",
    "emails":[
      {
        "templateName": "GOODBYE-01"
      }
    ]
  },
  {
    "name": "YOURE-FIRED-01",
    "channel": "email",
    "status":"active",
    "title": "Hello, welcome {firstname} {lastnane}",
    "parameters":[{
      "name": "firstname",
      "type": "string",
      "required": true
    },{
      "name": "lastname",
      "type": "string",
      "required": true
    }],
    "body":"Dear {firstname}, Pack your stuff and go",
    "emails":[]
  }
]

CodePudding user response：

You are very close actually. You just mixed up the variables and the field names. Note that $$ indicates variable in MongoDB aggregation pipeline. In your $let clause, you are using the value from emailtemplates.name to create variable name. So for the $lookup sub-pipeline you should compare $$name with $templateName, which refers to emails.templateName as you are looking up the emails collection.

The correct syntax should be like this:

db.emailtemplates.aggregate([
  {
    $match: {
      channel: "email"
    }
  },
  {
    $lookup: {
      from: "emails",
      let: {
        templateName: "$templateName",
        name: "$name"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$templateName",
                "$$name"
              ]
            }
          }
        },
        {
          $project: {
            "templateName": "$templateName",
            "name": "$name"
          }
        }
      ],
      as: "emails"
    }
  }
])

Mongo Playground