TIL PostgreSQL-style $facet

If you don't know yet, $facet in MongoDB allows you to run fork query into separate pipelines.

You can do this in PostgreSQL as well, using CTE and JSON functions.

SELECT ( SELECT json_agg(row_to_json) FROM ( SELECT row_to_json(t) FROM ( SELECT * FROM match_cte LIMIT ${limit} OFFSET ${(page - 1) * limit} ) t ) t1 ) result, ( SELECT COUNT(*) FROM match_cte ) "count" 

I think this is especially more important for expensive queries that you cannot fully index somehow...