Converting SQL to MQL?
Have you ever wondered how to create a complicated MongoDB aggregation pipeline queries from SQL queries?
There is a little know tip to convert SQL into MQL in MongoDB Compass.
Simply paste the SQL query into the query dialog box and hit the Generate button.
It will parse the query with AI (of course) and turn it into MQL. It helps if the table names match to Collection names and the field names match to column names. We used this generic SQL statement to count the most talked-about movies:
Select Movie.title, count(*) as "Comment Count"
from Movie right join comments on Movie._id = comments.movie_id
group by Movie.title
having count(*) > 5
order by count(*) desc
And we got this Mongo Query Language MQL aggregation pipeline:
[
{
"$group": {
"_id": "$movie_id",
"commentCount": {
"$sum": 1
}
}
},
{
"$match": {
"commentCount": {
"$gt": 5
}
}
},
{
"$lookup": {
"from": "movies",
"localField": "_id",
"foreignField": "_id",
"as": "movie"
}
},
{
"$unwind": "$movie"
},
{
"$project": {
"_id": 0,
"title": "$movie.title",
"Comment Count": "$commentCount"
}
},
{
"$sort": {
"Comment Count": -1
}
}
]
It’s driven by AI, so your mileage may vary, but if you are undergoing a SQL to Mongo migration project, this tip may help.