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 MongoDB Courses and Trainings | 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
5

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.