5

I have a collection called Post, which has an ID field called makeupProduct. This ID field is a foreign key to my MakeupProduct collection.

In MongoDB Compass, I'm trying to find all Posts

  1. where productUrl === null and
  2. populate the Makeup record along with it

Is this possible?

I have the 1st filter down, but don't know how to write the rest.

enter image description here

bigpotato
  • 24,574
  • 50
  • 160
  • 313
  • You can use the Aggregation (Aggregations TAB in Compass) to do a _join_ between _related_ collections using the aggregation pipeline stage [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html). In Compass you will be able to query, and you may have to do additional processing to update the data. – prasad_ Feb 21 '20 at 02:59
  • Does this answer your question? [How to join multiple collections with $lookup in mongodb](https://stackoverflow.com/questions/35813854/how-to-join-multiple-collections-with-lookup-in-mongodb) , You check that link on how to use `$lookup` operator which is native to MongoDB. But in case if you're using mongoose driver from Node.Js code you can use `.populate()` which does the similar job. Try using **robo3T** which is free to use + better as well & gives you shell kind of feel. – whoami - fakeFaceTrueSoul Feb 21 '20 at 03:29

1 Answers1

2
db.Post.aggregate([
    {
        $match: {
            productUrl: null
        }
    },
    {
        $lookup: 
        {
            from: "MakeupProduct",
            localField: "makeupProduct",
            foreignField: "_id",
            as: "makeupProduct"
        }
    },
    {
        $set: {
            makeupProduct: {
                $arrayElemAt: ["$makeupProduct", 0]
            }
        }
    }
])
Dĵ ΝιΓΞΗΛψΚ
  • 4,709
  • 3
  • 12
  • 25
  • What's the $set part for? – StudioWorks Aug 24 '21 at 20:20
  • 1
    @StudioWorks Because the `$lookup` pipe creates an array with all of the matching documents, and since it is only one document, the `$set` pipe sets the fields value to be the first element of the array. – Viriato Apr 05 '22 at 15:40