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;
-
1The MongoDB docs are really helpful. Give them a read and update your question with where you need help.fubar– fubar2021-06-27 22:21:40 +00:00Commented Jun 27, 2021 at 22:21
-
oh joining in MongoDB ... stackoverflow.com/questions/37575722/…Book Of Zeus– Book Of Zeus2021-06-27 23:24:07 +00:00Commented Jun 27, 2021 at 23:24
Add a comment
|
1 Answer
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
SQLas 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.
