Facets from SQL

facets.sql

-- This will return your results as json, and the first row will be a json object containing counts for field
-- values that can be used to build a faceted search/filter UI.

with t as (
	select * my_table where filter_field='value' -- this is your base query, everything else is based on it
),
field_counts as ( -- this gives you distinct values for your chosen fields from the result set from "t".  Add more unions for more faceted fields
	select json_agg(fc) from (
		select json_build_object(field, json_agg(json_build_object(val, num))) fc 
		from ( 
			select 'field1' as field, field1 as val, count(*) num from t group by field1
			union all
			select 'field2' as field, field3 as val, count(*) num from t group by field2
			union all
			select 'field3' as field, field3 as val, count(*) num from t group by field3
		) group by field
	)
)
select * from field_counts
union all
(select row_to_json(t) from t offset 20 limit 20); -- if you need to paginate, do it here, not in "t"