MongoDB.live, free & fully virtual. June 9th - 10th. Register Now MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Convert MySQL query into MongoDB

Hello Developers,
Hope you are fine. I am new here. I want to convert a MySQL query in MongoDB statement, please help me. My query is…

SELECT DISTINCT f1.*
FROM followers AS f1
INNER JOIN followers AS f2 on f1.user_id=f2.follower_id and f1.follower_id = f2.user_id
where f1.user_id=any user Id.

Thanks.

The following links has information and examples about how to map SQL to MongoDB - both the terminology and the queries. You will be using an Aggregation query to convert the SQL query.

1 Like

:wave:

Hi @Pradeep_Maurya and welcome to the community.

Being that your query is looking to join on multiple fields, this example in the documents can be used as the basis of your query.

As @Prasad_Saya, there are a couple of documents that give information on joining documents from multiple collections.

Note that depending on your use case however, you might get much better performance by embedding the fields of one document into the other.

1 Like

Welcome to the community @Pradeep_Maurya!

Rather than directly translating your MySQL schema and queries into MongoDB, I would encourage you to consider the best data model to suit your use case. As @Doug_Duncan mentioned, there are other options in MongoDB (such as embedding) which may offer better performance and less complex queries for common use cases.

Some helpful starting points for learning more about data modelling in MongoDB are:

If you do have a question about querying in MongoDB, please provide more information to help others understand what you are trying to achieve: version of MongoDB server, example documents, desired outcome, and what you have tried.

Regards,
Stennie

2 Likes

Hi @Stennie ,

Thanks for reply.

Actually I have a followers collection. I have put document with user_id and follower_id . I want to find all friends of a user X . (Condition For friend : all user follow to user X and X follow to all these users. ) .
I hope you understand my condition.

I have made this =>
db.follows.aggregate([

{

  $lookup:

     {

      from: "follows",

      let: { user_id: "$user_id", follower_id: "$follower_id" },

      pipeline: [

        { $match:

           { $expr:

              { $and:

                 [

                    { $eq: [ "$follower_id", "$$user_id" ] },

                    { $eq: [ "$user_id",  "$$follower_id" ] }

                 ]

              }

           }

          

        }

     ],

      as: "friends"

    }

},

{$match:{"user_id":user_id}}

]);

But not fulfill my condition . I have get all other user data who have no any friends with my user X
Thanks.