Getting JSON paginated results of a table SELECT

json_pagination.sql

SELECT json_build_object(
  'total', (SELECT n_live_tup FROM pg_stat_user_tables WHERE relname='sometable'),
  'count', count(sometable_rows.*),
  'offset', 0,
  'results', json_agg(row_to_json(sometable_rows))
)
FROM (SELECT * FROM sometable
ORDER BY "time"
LIMIT 10 OFFSET 0)
sometable_rows;
-- {"total": 2, "offset": 0, "count": 2, "results": [{"id": 1, "name": "Ogden Wernstrom"}, {"id": 2, "name": "Hubert Farnsworth"}]}
--
-- total is only accurate after VACUUM, 
-- but much faster than selecting the whole table to get a count

You could cast the whole thing to text with ::text to keep from having to deserialize and re-serialize, passing it to the http response raw.

You'll need Postgresql 9.4+