A few points here:
1) That’s a JOIN and Firestore is not unique among NoSQL databases for being bad at it.
2) That’s the code you’d use in a web browser to load Firestore data. That code also handles all of the API and Authentication code you’d normally put in front of another DB. It’s serverless. So that is a fully functional snippet, your SQL example needs an API layer and frontend deserialization code.
3) It does not make a new HTTP connection per request. It uses a long-lived gRPC connection.