Получение данных из трех таблиц в postgresql

#sql #postgresql

Вопрос:

Я новичок в postgresql. У меня есть три таблицы

Таблица 1: учитель

 tid   | t_name  | t_gender
1     | Joe     | Male
2     | Mantera | Female

Таблица 2: school_location

 school_id     | location
    1         | Location1
    2         | Location2
    3         | Location3
    4         | Location4
    5         | Location5
    6         | Location6

Таблица 3: teachers_school_location

 school_id     |  tid  |      timings
     1        |   2   | 08:00 AM - 01:00 PM
     2        |   2   | 02:00 PM - 05:00 PM
     3        |   2   | 06:00 PM - 08:00 PM
     4        |   1   | 09:00 AM - 12:00 PM
     5        |   1   | 02:00 PM - 04:00 PM

Я хочу написать запрос для получения данных из этих трех таблиц следующим образом: (вывод в формате json путем вызова API)

Когда пользователь выбирает tid = 1, я хочу получить такой вывод

Когда пользователь выбирает tid = 2, я хочу получить такой вывод

Я использовал ВНУТРЕННИЕ СОЕДИНЕНИЯ для получения выходных данных (запрос приведен ниже), но проблема в том, что данные из таблицы teacher повторяются для каждого местоположения.

 select 
   teacher.t_name
   , teacher.t_gender
   , school_location.location
   , teachers_school_location.timings 
from teacher 
inner join teachers_school_location 
   on teacher.tid = teachers_school_location.tid 
inner join school_location 
   on school_location.school_id = teachers_school_location.school_id
where teacher.tid = 2;

Его вывод:

но мне нужен такой вывод:

Как я могу добиться такого результата?

1. Я не совсем уверен, о чем вы спрашиваете. Выходные данные SQL и JSON выглядят как представляющие в целом эквивалентные данные. Результирующий набор SQL никогда не будет выглядеть как JSON, поскольку это принципиально разные способы отображения данных. Вы спрашиваете, как преобразовать результирующий набор SQL в JSON?

2. Вы должны использовать функции json_object_agg postgresql.org/docs/9.4/functions-aggregate.html

Ответ №1:

как вы знаете, вы можете использовать встроенные функции JSON в Postgres для подготовки вашего JSON следующим образом:

  SELECT
    json_build_object('t_name', t.t_name, 't_gender', t.t_gender, 'locations', locations_json)
FROM
    (
        SELECT
            tl.tid
            , json_object_agg(location, timings) locations_json
        FROM
            school_location               l
            JOIN teachers_school_location tl
                ON l.school_id = tl.school_id
        WHERE
            tl.tid = 2
        GROUP BY
            tl.tid
    )            tt
    JOIN teacher t
        ON t.tid = tt.tid;