Как правильно получить данные из двух таблиц одной строкой?

Question

[Igor Tikhonov]

Здравствуйте. Уже несколько дней бьюсь с простым вопросом, не могу найти ответ в интернете (не знаю, какую формулировку еще использовать, чтобы найти нужное).

Есть две PostgreSQL таблицы:
users - список пользователей

• id
  
• email
  
• name
  

socials - список прикрепленных социальных сетей ко всем пользователям. Выбирается сравниваем users.id = socials.user_id

• social_id
  
• user_id
  
• type
  
• token
  

Нужно вывести данные о пользователе, включая все прикрепленные социальные сети.

У пользователя может быть прикреплено несколько социальных сетей.
Если делать запрос так:

select "users"."id", "users"."name", "socc_acc"."type", "socc_acc"."token" from "users" left join (select "social_id", "user_id", "type", "token" from "socials" where "user_id" = 100) as "socc_acc" on "socc_acc"."user_id" = "users"."id" where "id" = 100 group by "socc_acc"."social_id", "users"."id"

100 - id выбираемого пользователя;
при наличии нескольких социальных сетей результат следующий:

[{
    id: 100,
    name: 'Haylee Simonis',
    type: 1,
 token: 'something'
  },
  {
    id: 100,
    name: 'Haylee Simonis',
    type: 2,
 token: 'something2'
  }]

т.е. возвращается две строки наполненные одним и тем же, различия только в полях type и token.
Есть ли способ вывести все данные одной строкой без повторяющихся данных пользователя?

Пробовал способ объединения функцией ARRAY_AGG:

select "0"."id", "0"."email", "0"."name, ARRAY_AGG("1"."type") as social_type, ARRAY_AGG("1"."token") as social_token from "users" as "0", "socials" as "1" where "0"."id" = 100 and "1"."user_id" = 100 group by "0"."id"

в таком случае, результат ближе к тому, что хотелось бы видеть:

[{
    id: 100,
    email: '[e-mail]',
    name: 'Haylee Simonis',
    social_type: [ 2, 1 ],
    social_token: [ 'something2', 'something' ]
  }]

Но возможно есть более правильный способ сделать что-то вроде этого:

[{
    id: 100,
    email: '[e-mail]',
    name: 'Haylee Simonis',
    socials: [{
 type: 1,
token: 'something'
 },
{
type: 2,
token: 'something2'
}]
  }]

Работаю в Node.js (Koa) с помощью Knex, если это важно.

Заранее благодарю за помощь.
Также буду признателен если вы подскажете актуальную книгу или статью, где объясняются подобные азы.

P.S. Я не имел дело со SQL раньше, поэтому, возможно, вообще неправильно подхожу к вопросу.
Возможно стоит делать два разных, не связанных между собой, запроса?

UPD Смог найти такое решение:

SELECT "social_id", array_agg('[' || type || ',' || token || ']') AS "socials" FROM "socials" WHERE "user_id" = 105 GROUP BY "social_id";

результат:

socials: [
      '[2,something2]',
      '[1,something]'
    ]

Еще лучше, но синтаксис запроса выглядит не очень. Может, все таки, есть более изящное решение?

Answer 1

[Igor Tikhonov]

Спустя три дня поиска все-таки пришел к такому решению, может кому-то будет полезно:

SELECT "social_id", json_agg(json_build_object('type', type, 'token', token) AS "socials" FROM "socials" WHERE "user_id" = 105 GROUP BY "social_id";

json_build_object принимает в качестве аргументов имя ключа для объекта и значение (имя ключа в таблице) через запятую.

Результат:

[
 { type: 2, token: 'something2' },
 { type: 1, token: 'something' }
]

Остается только объединить с запросом выше, чтобы социальные сети выводились таким образом при запросе данных пользователя.
Важно указать все ключи из таблицы пользователя в ORDER BY, иначе снова начнется дублирование.