Performance is definitely an important aspect, but coming from a background working at smaller companies, bootstrapping time and development time are a factor as well -- and while Postgres is easy to set up if you're familiar with it (and I dare say it makes DB design fairly straightforward), Mongo and other NoSQLs are pretty objectively easier to just get working if you aren't fluent in relational databases.

So, I've been trying to figure this out a bit lately.

How would you go about writing a customer facing query builder that is analogous to the MongoDB aggregation pipeline with SQL?

With MongoDB I could conceivably generate/store a JSON object for such a query. In SQL it seems a lot more obtuse to do.

This is a very bold comment, explain how you scale write, do HA in pg?