INNER JOIN 3 tables with GROUP BY and Aggregate Function

Question

I have three tables I'm trying to inner join with aggregate functions and group by. Both of my times tables have a foreign key that refers to the id of the spaces table. This is what my data looks like:

Spaces  
id  address
1   'Address 1, city, state, zip'    
2   'Address 2, city, state, zip'  
3   'Address 3, city, state, zip'  
4   'Address 4, city, state, zip'  
5   'Address 5, city, state, zip'  

Times1  
id1 spaces_id  start   end
1   1          '10am'  '1pm'  
2   1          '11am'  '7pm'  
3   1          '1am'   '1pm'  
4   2          '10am'  '9pm'  
5   2          '8am'   '1pm'  

Times2  
id2 spaces_id  start   end  
1   1          '10am'  '1pm'  
2   1          '11am'  '7pm'  
3   1          '1am'   '1pm'  
4   2          '10am'  '9pm'  
5   2          '8am'   '1pm'  

I want my outputted data to look like this (with the times from both tables combined into an array of times with a start and end property):

Combined
id  address                         times
1   'Address 1, city, state, zip'   [{start: '10am', end: '1pm'}, {start: '11am', end: '7pm'}, ...]
2   'Address 2, city, state, zip'   [{start: '10am', end: '9pm'}, {start: '8am', end: '1pm'}, ...]
3   'Address 3, city, state, zip'   [...]
4   'Address 4, city, state, zip'   [...]
5   'Address 5, city, state, zip'   [...] 

I've successfully combined this data with the spaces table and ONE time table with the following query:

SELECT s.*, JSON_STRIP_NULLS(JSON_AGG(JSON_BUILD_OBJECT('start', t.start, 'end', t.end))) 
AS times 
FROM spaces s 
LEFT OUTER JOIN times t 
ON s.id = t.space_id 
GROUP BY s.id

Answer 1

There are a few inconsistencies between your structure and the query you've written, so I've made a few assumptions. Thus, you might need to tweak the query.

SELECT
    s.id,
    s.address,
    COALESCE(json_agg(d.json) filter (WHERE d.json IS NOT NULL), '[]')
FROM spaces s
LEFT JOIN (
    SELECT
        spaces_id,
        json_build_object('start', start, 'end', "end") AS json
    FROM (
        SELECT * FROM times1
        UNION ALL
        SELECT * FROM times2
    ) t
) d ON d.spaces_id = s.id
GROUP BY s.id, s.address;

Explanation:

In a first step we construct a dataset d by unioning the two times tables, such that we get a space id and the desired JSON object.

Then we select the spaces and left join above data set d. For spaces that do not have any data in d we do not want to json_agg, which would result in [null]. Instead, we filter those out, which would result in null. To prevent null we COALESCE to an empty JSON array.