CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-query
user10181542
user10181542

Reputation: 1370

Mongodb Join then Find

I want to Join a collection then find in mongodb.

Here is my collection:

COLORS:

{id: 1, color: 'red'}
{id: 2, color: 'blue'}

PRICES:

{price_id: 1, price: 4}
{price_id: 2, price: 5}

How do I find the color who has the price of 5 dollars?

Here is my solution (which didn't work):

db.collection('colors').aggregate([
    {
        $lookup:
            {
                from: 'prices',
                localField: 'id',
                foreignField: 'price_id',
                as: 'info'
            },
        $match:
            {
                price: 5
            }
    }
]).toArray((res, err) => {
    if (res) {
        console.log(res);
    } else {
        console.log(err);
    }
})

Upvotes: 0

Views: 47

Answers (2)

HbnKing
HbnKing

Reputation: 1882

it was caused by your $match didnt work !

the price is in info field ,it is inner field in an array .

the data after join is like this 

{ "_id" : ObjectId("5d79125fcc36fb6c276f2edd"), "id" : 1, "color" : "red", "info" : [ { "_id" : ObjectId("5d79128ccc36fb6c276f2edf"), "price_id" : 1, "price" : 4 } ] }
{ "_id" : ObjectId("5d79125fcc36fb6c276f2ede"), "id" : 2, "color" : "blue", "info" : [ { "_id" : ObjectId("5d79128ccc36fb6c276f2ee0"), "price_id" : 2, "price" : 5 } ] }

you can change your $match fuction

 db.getCollection("colors").aggregate(
[
{$lookup: {  from: 'prices', 
              localField: 'id',
              foreignField: 'price_id',
              as: 'info'}
          },
{$unwind :"$info"},
{$match:{"info.price":5}}
]
)

Upvotes: 0

Ashok
Ashok

Reputation: 2932

If you have MongoDB 3.6 or above

db.colors.aggregate([
  { $lookup:{
     from: "prices",
     let: { id: "$id"},
     pipeline: [
        { $match: { 
            $expr: {
              $and:[ { $eq: ["$price_id", "$$id"]}, { $eq: ["$price", 5] }]
            }
        }},
      ],
      as: "info"
  }}
])

Upvotes: 1

Related Questions