CODEWITHSUNDEEP
mongodb
Niyojita
Niyojita

Reputation: 31

How do I find records from one collection based on field of other collection in MongoDB

How do I find records from one collection based on field of other collection in MongoDB (e.g collection A and collection B have a common field as userid just like primary-foreign key concept in SQL)

Like in MYSQL I can use a join query

SELECT * FROM a
inner join b on a.id=b.id
where id=12

How can this be achieved in MongoDB?

Upvotes: 1

Views: 5710

Answers (3)

badrik patel
badrik patel

Reputation: 153

aggregate returns a POJO object so deleting is not possible on it but you can still achieve this by simply writing two query first is aggregate and second is deleteMany

db.collection(a).aggregate([
        {'$match':{id:12}},//Optional if you want or you can leave empty
        {'$lookup':{
            from:'b',
            localField:'id',//fildname of a
            foreignField:'id',//field name of b
            as:'details' // you can also use id fiels it will replace id with the document
        }},
        { $match : { "details" : { $eq : []}}}
])

db.collection(a).deleteMany({id:{$in:[array of ids you get from above query]}})

I am little unsure about this if it works.
Also please upvode my answers bro which was useful for you or helped you.

Upvotes: 1

badrik patel
badrik patel

Reputation: 153

As MongoDB is a NoSQL database so there is NO INNER JOIN, but still you can use lookup to achieve your goal. In lookup, the as field specifies where you want your data it can be a new field or you can replace the existing field.

Your conditions can be fulfilled of filtering data by simply adding $match in last

db.collection(a).aggregate([
        {'$match':{id:12}},//Optional if you want or you can leave empty
        {'$lookup':{
            from:'b',
            localField:'id',//fildname of a
            foreignField:'id',//field name of b
            as:'details' // you can also use id fiels it will replace id with the document
        }},
        { $match : { "details" : { $ne : []}}}
])

Upvotes: 2

badrik patel
badrik patel

Reputation: 153

You can achieve this in MongoDB by using aggregate query it can be used to fetch data from two collections.

Link to documentation:-https://docs.mongodb.com/manual/aggregation/

 db.collection(a).aggregate([
        {'$match':{id:12}},//Optional if you want or you can leave empty
        {'$lookup':{
            from:'b',
            localField:'id',//fildname of a
            foreignField:'id',//field name of b
            as:'details'
        }}
])

OR

If you are using some framework like Node Js and you have provided proper reference while creating schema you can populate documents from another collection to another

Link:-https://mongoosejs.com/docs/populate.html

Upvotes: 2

Related Questions