PostgreSQL JSON generation example: Generating an array of time arrays, where each outer array element represent a date and each inner element represents a time on the date represented by its parent element. This is useful for generating compact JSON if the returned date range is known. (The date range can easily be known as it is user specified…

postgres-times-json.sql

with
	a as (select * from generate_series(0, 6) s), -- 6 is user specified number of days - 1
	b as (select ('2013-03-01'::date + s * interval '1 day')::date appt_date from a), -- 2013-03-01 is user specified start date
	c as (select * from generate_series('2013-03-02 00:00'::timestamp without time zone, '2013-03-05 23:59'::timestamp without time zone, '10 minutes') appt), -- Dummy data - appointment times
	d as (select appt::date appt_date, to_char(appt, 'HH24:MI') appt_time from c), -- Split out the date and time
	e as (select appt_date, json_agg(appt_time order by appt_time) times from d group by appt_date), -- Aggregate times into an array per day
	f as (select appt_date, coalesce(times, '[]'::json) times from b left join e using (appt_date)) -- Left join onto the generated date series to produce an array for all days including empty ones
select json_agg(times order by appt_date) from f -- Aggregate the result into a single array of arrays