Converting this SQL query to Mongodb query

Question

I have come up with a SQL query since i am more familiar with it, but i need help converting this query into a Mongodb query.

So, I have 4 collections :

Post


{
    title: String,
    content: String,
    sub_id: String,
}

User


{
    user_id: String,
    user_name: String,
}

Sub


{
    sub_id: String,
    sub_name: String,
}

Follow


{
    sub_id: String,
    user_id: String,
}

The idea is to retrieve all posts that come from subs that the user is following.

And here is the SQL query that seems to fit the idea:

SELECT Post.* 
FROM Post
JOIN Subs ON Post.sub_id = Subs.sub_id
JOIN Follow ON Post.sub_id = Follow.sub_id
JOIN User ON Follow.user_id = User.user_id
WHERE User.user_id = "user id here"

I should probably use the aggregate $lookup but i have never used it and i am quite lost with the process.

When you migrate an application from relational SQL to MongoDB then it is usually a bad design if you convert the tables one-by-one to a collection. Typically an application has much less collections than you had number of tables in RDBMS. So, better review the collection design. — Wernfried Domscheit
– Wernfried Domscheit, Commented Apr 3, 2021 at 20:51

Dheemanth Bhat · Accepted Answer · 2021-04-03 20:26:14Z

1

Try this:

let userId = "user1";

db.Post.aggregate([
    {
        $lookup: {
            from: "Sub",
            localField: "sub_id",
            foreignField: "sub_id",
            as: "Sub"
        }
    },
    { $unwind: "$Sub" },
    {
        $lookup: {
            from: "Follow",
            localField: "sub_id",
            foreignField: "sub_id",
            as: "Follow"
        }
    },
    { $unwind: "$Follow" },
    {
        $lookup: {
            from: "User",
            let: { user_id: "$Follow.user_id" },
            pipeline: [
                {
                    $match: {
                        user_id: userId,
                        $expr: { $eq: ["$user_id", "$$user_id"] }
                    }
                }
            ],
            as: "User"
        }
    },
    { $unwind: "$User" }
])

answered Apr 3, 2021 at 20:26

Dheemanth Bhat

4,5022 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting this SQL query to Mongodb query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related