Converting this mysql query to mongoDB

Working with Data
,
#1

Good evening,
I’m searching to translate this mysql query in mongodb, how is it ??

select d.surname, d.forename, c.name, rcs.year, rcs.name, r.raceId, r.fastestLapSpeed
from ((results r join drivers d on r.driverId = d.driverId) 
join constructors c on c.constructorId = r.constructorId) 
join races rcs on r.raceId = rcs.raceId
where r.fastestLapSpeed >  ( 
								select avg(rr.fastestLapSpeed)
                                from results rr
                                where rr.raceId = r.raceId
                                group by rr.raceId
							)
#2

Please post sample documents from all collections involve.

Please share what you have tried so far and indicate how it failed. This will prevent us from working on solutions that you already know it fails.

The M100: MongoDB for SQL Pros | MongoDB University is a good starting point.

#3 
db.resultsConstructorsStatus.aggregate([
    {
        $lookup: {
           from: "drivers",
           let: {flspeed: "$fastestLapSpeed", rcsId: "$raceId"},
           pipeline: [
                { $group : { _id: "$raceId", avgLapSpeed: {$avg: "$fastestLapSpeed" } }},
                { $match : { $and: [{avgLapSpeed: {$lte: "fastestLapSpeed" }}, {$eq: {rcsId, "$raceId"}}] }}
           ],
           localField: "driverId",
           foreignField: "driverId",
           as: "drivers_details"
        }
    }, 
    {$unwind: "$drivers_details"},
    {$project: {_id: 0, "drivers_details.forename":1, "drivers_details.surname":1, "constructor_info.name":1, "raceId":1, "fastestLapSpeed":1}}
])
1 Like
#4

To experiment and help you we in the absence of sample documents, we would need to manually create document that matches the field names you are using. That’s very tedious.

In your original SQL, there were mentions of table drivers, constructors and races but in the Mongo version, it looks like you only have 2 collections. The mapping for drivers is quite obvious, but how is resultsConstructorsStatus related to constructors and races. Sample documents could help understanding that too.

1 Like