fetch from mongo collection based on value in other collection

user_12496104 0
Viewed 46

I have 2 mongodb collections: "users" and "posts".

// users
{
  _id,
  username,
  password,
  privacy_level
}

// posts
{
  _id,
  user_id,
  text,
  timestamp
}

Is it possible to fetch all posts based on the user's privacy level ("public"). In SQL, I would do something like this:

SELECT 
    _id, 
    text, 
    timestamp 
FROM 
    posts 
JOIN 
    users 
  ON 
    posts.user_id = users._id 
WHERE 
    users.privacy_level = 'public'

Is it possible to achieve this kind of query logic in mongodb?

mongodb join
2 Answers

Yes, you can achieve this quite easily using $lookup which is the Mongo equivilant of SQL join. here is an example by starting the query from the users collection:

db.users.aggregate([
  {
    $match: {
      privacy_level: "public"
    }
  },
  {
    "$lookup": {
      "from": "posts",
      "localField": "_id",
      "foreignField": "user_id",
      "as": "posts"
    }
  },
  {
    $unwind: "$posts"
  },
  {
    $project: {
      _id: "$posts._id",
      text: "$posts.text",
      timestamp: "$posts.timestamp"
    }
  }
])

Mongo Playground

user_107703700

You can try $lookup operator to join collection,

  • $lookup join users collection
  • $match privacy_level is public
  • $project remove extra fields
db.posts.aggregate([
  {
    $lookup: {
      from: "users",
      localField: "user_id",
      foreignField: "_id",
      as: "user"
    }
  },
  { $match: { "user.privacy_level": "public" } },
  {
    $project: {
      user: 0,
      user_id: 0
    }
  }
])

Playground

user_89871280
Related