TIL PostgreSQL-style $facet

#todayilearned #postgres #sql #database

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"