How do I write this SQL query in Mongodb syntax?

Question

How do I write this SQL query in Mongodb syntax?

select a.title 
from movies as a 
inner join ratings as b on a.movieId=b.movieId 
where a.genres like '%Children%' 
and b.rating>3 
group by a.title;

Answer 1

For this SQL Query:

select movies.title 
from movies 
inner join ratings on movies.movieId=ratings.movieId 
where movies.genres like '%Children%' 
and ratings.rating>3 
group by movies.title;

The equivalent MongoDB Query is: (included sort and limit as well, remove if not required)

db.movies.aggregate(
    [
        {
            "$lookup" : {
                "from" : "ratings",
                "localField" : "movieId",
                "foreignField" : "movieId",
                "as" : "ratings_docs"
            }
        },
        {
            "$match" : {
                "ratings_docs" : {
                    "$ne" : [ ]
                }
            }
        },
        {
            "$addFields" : {
                "ratings_docs" : {
                    "$arrayElemAt" : [
                        "$ratings_docs",
                        0
                    ]
                }
            }
        },
        {
            "$match" : {
                "genres" : /^.*Children.*$/is,
                "ratings_docs.rating" : {
                    "$gt" : 3
                }
            }
        },
        {
            "$group" : {
                "_id" : {
                    "title" : "$title"
                }
            }
        },
        {
            "$project" : {
                "title" : "$_id.title"
            }
        },
        {
            "$sort" : {
                "_id" : -1
            }
        },
        {
            "$limit" : 100
        }
    ]
)

You can also generate the equivalent mongodb query anytime from the tools. like in my case I am using No Sql Booster for MongoDB. I am also using free version of No Sql Booster for MongoDB

Steps that you can follow:

• STEP 1: Connect your Mongo DB Query String, and select this SQL as shown in image:

• STEP 2: You wll see a text area with mb.runSQLQuery() as shown below. You can write any query, and click on Code. The code will be generated below as shown in image. Don't worry, it converts all the queries, doesnot connect on the database.