DEV Community

Pacharapol Withayasakpunt
Pacharapol Withayasakpunt

Posted on

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"
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)