$match in $lookup result

Question

I have next mongo code:

db.users.aggregate([
    { 
        $match: { 
            $and: [
                { UserName: { $eq: 'administrator' } }, 
                { 'Company.CompanyName': { $eq: 'test' } }
            ]                   
        } 
    },
    { 
        $lookup: { 
            from: "companies", 
            localField: "CompanyID", 
            foreignField: "CompanyID", 
            as: "Company" 
        } 
    },
])

The $lookup part of the code working great. I got next result:

But if I add $match to the code, it brings nothing.

I found that the problem is in the second match: { 'Company.CompanyName': { $eq: 'test' } }, but I can not realize what is wrong with it. Any ideas?

UPDATE:

I had also tried $unwind on the $lookup result, but no luck:

db.users.aggregate([
    { 
        $match: { 
            $and: [
                { UserName: { $eq: 'administrator' } }, 
                { 'Company.CompanyName': { $eq: 'edt5' } }
            ] 
        } 
    },
    {   unwind: '$Company' },
    { 
        $lookup: { 
            from: 'companies', 
            localField: 'CompanyID', 
            foreignField: 'CompanyID', 
            as: 'Company' 
        } 
    },
])

Two points: 1: can you copy in an example of a record you think it should be returning? Your screenshot isn't ideal because the Company data is cut off before the CompanyName field is shown 2: do you get the same problem if you run with just the $match section i.e. without the $unwind or the $lookup? — Vince Bowdren, Feb 07 '17 at 13:31
@VinceBowdren 1) Added new image with full company object.2) $match with username only runs good, but the company field become only with lookup, so how could I test it without $lookup? — AlexBerd, Feb 07 '17 at 13:36
You'll need to $unwind `Company` after $lookup. Add a $match stage on company field after $unwind — s7vr, Feb 07 '17 at 13:40
@Veeram You are genius man. I broke my head nearly 5 hours... OMG. Post you answer I will accept it!!! — AlexBerd, Feb 07 '17 at 13:41

chridam · Accepted Answer · 2017-02-07T14:08:01.193

With MongoDB 3.4, you can run an aggregation pipeline that uses the $addFields pipeline and a $filter operator to only return the Company array with elements that match the given condition. You can then wrap the $filter expression with the $arrayElemAt operator to return a single document which in essence incorporates the $unwind functionality by flattening the array.

Follow this example to understand the above concept:

db.users.aggregate([
    { "$match": { "UserName": "administrator" } },
    { 
        "$lookup": { 
            "from": 'companies', 
            "localField": 'CompanyID', 
            "foreignField": 'CompanyID', 
            "as": 'Company' 
        } 
    },
    {
        "$addFields": {
            "Company": {
                "$arrayElemAt": [
                    {
                        "$filter": {
                            "input": "$Company",
                            "as": "comp",
                            "cond": {
                                "$eq": [ "$$comp.CompanyName", "edt5" ]
                            }
                        }
                    }, 0
                ]
            }
        }
    }
])

You beat me to it. You may need to move the `$addFields` stage after lookup and remove the `CompanyName` part from `$match` . I think the `CompanyName` is the field in the looked up collection — s7vr, Feb 07 '17 at 13:58
@Veeram You are right, thanks for noticing. I've updated the answer to include the OP's original desired query. Cheers! — chridam, Feb 07 '17 at 14:08

score 36 · Answer 2 · answered May 28 '19 at 14:15

Below answer is for mongoDB 3.6 or later.

Given that:

You have a collection users with a field CompanyID and a collection of companies with a field CompanyID
you want to lookup Companies on Users by matching CompanyID, where additionally:
- each User must match condition: User.UserName equals administrator
- each Company on User must match condition: CompanyName equals edt5

The following query will work for you:

  db.users.aggregate([
    { $match: { UserName: 'administrator' } },
    {
      $lookup: {
        from: 'companies',
        as: 'Company',
        let: { CompanyID: '$CompanyID' },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ['$CompanyID', '$$CompanyID'] },
                  { $eq: ['$CompanyName', 'edt5'] },
                ]
              }
            }
          }
        ]
      }
    },
  ])

Explanation: This is the way to perform left join queries with conditions more complex than simple foreign / local field equality match.

Instead of using localField and foreignField, you use:

let option where you can map local fields to variables,
pipeline option where you can specify aggregation Array.

In pipeline you can use $match filter, with $expr, where you can reuse variables defined earlier in let.

More info on $lookup

Nice tutorial

Regarding `{ $eq: ['$CompanyID', '$$CompanyID'] },`. The left side is the field in `companies` — AliAvci, Jan 29 '20 at 20:28
Thank for all the explanation. This answers is indeed the best way to do it with latest versions. — Frederiko Ribeiro, Oct 14 '20 at 11:04

score 0 · Answer 3 · answered Jul 13 '20 at 12:11

here is code for fitering array inside lookup.

 const userId = req.userData.userId;
const limit = parseInt(req.params.limit);
const page = parseInt(req.params.page);

Collection.aggregate([
 { $match: {} },
  { $sort: { count: -1 } },
  { $skip: limit * page },
  { $limit: limit },
  {
    $lookup: {
      from: Preference.collection.name,
      let: { keywordId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$keyword", "$$keywordId"] },
                {
                  $eq: ["$user", mongoose.Types.ObjectId(userId)],
                },
              ],
            },
          },
        },
      ],
      as: "keywordData",
    },
  },
  {
    $project: {
      _id: 0,
      id: "$_id",
      count: 1,
      for: 1,
      against: 1,
      created_at: 1,
      updated_at: 1,
      keyword: 1,
      selected: {
        $cond: {
          if: {
            $eq: [{ $size: "$keywordData" }, 0],
          },
          then: false,
          else: true,
        },
      },
    },
  }])

$match in $lookup result

3 Answers3

Linked