INNER JOIN 3 таблицы с GROUP BY и функцией агрегирования
У меня есть три таблицы, которые я пытаюсь объединить с агрегатными функциями и группировать по ним. Обе мои таблицы времени имеют внешний ключ, который ссылается на идентификатор таблицы пробелов. Вот как выглядят мои данные:
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'
Я хочу, чтобы мои выходные данные выглядели так (с временем из обеих таблиц, объединенных в массив времени со свойством start и end):
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' [...]
Я успешно объединил эти данные с таблицей пробелов и ОДНОЙ таблицей времени со следующим запросом:
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
@fphilipe да, большое спасибо!
Ответы 1
Между вашей структурой и написанным вами запросом есть несколько несоответствий, поэтому я сделал несколько предположений. Таким образом, вам может потребоваться настроить запрос.
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;
Объяснение:
На первом этапе мы создаем набор данных d, объединяя две таблицы умножения, чтобы получить идентификатор пространства и желаемый объект JSON.
Затем мы выбираем пробелы и левое соединение над набором данных d. Для пространств, в которых нет данных в d, мы не хотим json_agg, что приведет к [null]. Вместо этого мы отфильтровываем их, что приводит к null. Чтобы предотвратить null мы COALESCE в пустой массив JSON.
@TaylerBantle, мой ответ как-то помог?