CODEWITHSUNDEEP
node.jsmongodbaggregation-framework
Guillaume
Guillaume

Reputation: 391

How to have a conditional aggregate lookup on a foreign key

After many many tries, I can't have a nice conditional aggregation of my collections.

I use two collections :

races which have a collection of reviews.

I need to obtain for my second pipeline only the reviews published. I don't want to use a $project. Is it possible to use only the $match ? When I use localField, foreignField, it works perfect, but I need to filter only the published reviews. I struggled so much on this, I don't understand why the let don't give me the foreignKey. I tried : _id, $reviews, etc..

My $lookup looks like this : 

{
    $lookup: {
      from: "reviews",
      as: "reviews",
      let: { reviewsId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                // If I comment the next line, it give all the reviews to all the races
                { $eq: ["$_id", "$$reviewsId"] },
                { $eq: ["$is_published", true] }
              ]
            }
          }
        }
      ]

      // localField: "reviews",
      // foreignField: "_id"
    }
  },

Example of a race : 

{  
   "description":"Nice race",
   "attendees":[  

   ],
   "reviews":[  
      {  
         "$oid":"5c363ddcfdab6f1d822d7761"
      },
      {  
         "$oid":"5cbc835926fa61bd4349a02a"
      }
   ],
   ...
}

Example of a review : 

{  
   "_id":"5c3630ac5d00d1dc26273dab",
   "user_id":"5be89576a38d2b260bfc1bfe",
   "user_pseudo":"gracias",
   "is_published":true,
   "likes":[],
   "title":"Best race",
   "__v":10,
   ...
}

I will become crazy soon :'(... How to accomplish that ?

Upvotes: 1

Views: 1958

Answers (2)

Ashh
Ashh

Reputation: 46481

First you have to take correct field to get the data from the referenced collection i.e. reviews. And second you need to use $in aggregation operator as your reviews field is an array of ObjectIds.

db.getCollection('races').aggregate([
  { "$lookup": {
    "from": "reviews",
    "let": { "reviews": "$reviews" },
    "pipeline": [
      { "$match": {
        "$expr": { "$in": [ "$_id", "$$reviews" ] },
        "is_published": true
      }}
    ],
    "as": "reviews"
  }}
])

Upvotes: 1

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

Your problem is this line:

        { $eq: ["$is_published", true] }

You are using this document _id field to match the reviews one.

The correct version looks like this:

(

    [
        { 
            "$unwind" : "$reviews"
        }, 
        { 
            "$lookup" : {
                "from" : "reviews", 
                "as" : "reviews", 
                "let" : {
                    "reviewsId" : "$reviews"
                }, 
                "pipeline" : [
                    {
                        "$match" : {
                            "$expr" : {
                                "$and" : [
                                    {
                                        "$eq" : [
                                            "$_id", 
                                            "$$reviewsId"
                                        ]
                                    },
                                     { $eq: ["$is_published", true] }
                                ]
                            }
                        }
                    }
                ]
            }
        }
    ], 
);

and now if your want to restore the old structure add:

{
  $group: {
           _id: "$_id",
           reviews: {$push: "$reviews"},
   }
}

Upvotes: 1

Related Questions