Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

$match in $lookup result

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:

enter image description here enter image description here

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'          }      }, ]) 
like image 589
AlexBerd Avatar asked Feb 07 '17 13:02

AlexBerd


People also ask

What does $match do in MongoDB?

The MongoDB $match operator filters the documents to pass only those documents that match the specified condition(s) to the next pipeline stage.

What can the $match aggregation stage be used for?

The $match stage of the pipeline can be used to filter documents so that only ones meeting certain criteria move on to the next stage. In this article, we'll discuss the $match stage in more detail and provide examples that illustrate how to perform match aggregation in MongoDB.

Can we use match in lookup in MongoDB?

Equality Match with a Single Join Condition Starting in MongoDB 5.1, the collection specified in the from parameter can be sharded. Specifies the field from the documents input to the $lookup stage. $lookup performs an equality match on the localField to the foreignField from the documents of the from collection.

What are lookup in aggregation for MongoDB?

js. The $lookup operator is an aggregation operator or an aggregation stage, which is used to join a document from one collection to a document of another collection of the same database based on some queries. Both the collections should belong to the same databases.


Video Answer


2 Answers

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                 ]             }         }     } ]) 
like image 125
chridam Avatar answered Oct 01 '22 03:10

chridam


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

like image 34
Jan Franciszek Cieślak Avatar answered Oct 01 '22 01:10

Jan Franciszek Cieślak