Сергей c0re

Для построения такого JSON на SQL вам нужны функции json_agg() / json_build_object(), далее обычные запросы с объединениями и группировкой.

Пример как собрать JSON author

SELECT json_build_object(
    'account_id', id,
    'first_name', first_name,
    'last_name', last_name
  ) AS author
  FROM accounts

post собирается как

json_build_object(
    'post_id', p.id,
    'body', p.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )

при объединении таблиц posts и accounts
через json_agg собираем в массив

SELECT json_agg(json_build_object(
    'post_id', p.id,
    'body', p.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )) AS posts
  FROM posts p
  JOIN accounts a ON a.id = p.author_id

комментарии аналогично, но с группировкой по постам - post_id

SELECT json_agg(json_build_object(
    'comment_id', c.id,
    'body', c.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )) AS comments
  FROM comments c
  JOIN accounts a ON a.id = c.author_id
  GROUP BY post_id

далее объединяем комментарии с постами через post_id = id
для удобства комментарии вынес в CTE (общее табличное выражение)

WITH cmts AS (
SELECT json_agg(json_build_object(
    'comment_id', c.id,
    'body', c.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    )
  )) AS comments,
  post_id
  FROM comments c
  JOIN accounts a ON a.id = c.author_id
  GROUP BY post_id
)
SELECT json_agg(json_build_object(
    'post_id', p.id,
    'body', p.body,
    'author', json_build_object(
      'account_id', a.id,
      'first_name', a.first_name,
      'last_name', a.last_name
    ),
    'comments', COALESCE(c.comments, '[]')
  )) AS "posts with comments"
  FROM posts p
  JOIN accounts a ON a.id = p.author_id
  LEFT JOIN cmts c ON c.post_id = p.id

см. пример sqlfiddle

а вы доку пробовали смотреть, как добавить default value, constraint, foreign key ?

ALTER TABLE salary ALTER COLUMN salary_date SET DEFAULT current_date;

ALTER TABLE salary
ADD CONSTRAINT FK_Salary_Employee_ID FOREIGN KEY(employee_id)
REFERENCES EMPLOYEE(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE

Таблица EMPLOYEE разумеется должна существовать, а поле employee_id должно быть уникальным.

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=238a...

не знаю как вы сделали объединение по своему прошлому вопросу, но надо было сначала отобрать уникальные записи из первой таблицы, а затем уже заджойнить к ней вторую таблицу и агрегировать value в массив, тогда там где во второй таблице ничего нет к первой в массиве всего лишь один элемент null, соответственно берем первый элемент массива и смотрим его на null и т.д.

в-общем как-то так:

SELECT
  t.a1, t.a2, t.a3,
  CASE WHEN ( ARRAY_AGG(t2.value) )[1] IS null THEN null ELSE ARRAY_AGG(t2.value) END AS arr
FROM (
    SELECT 
      t1.a1,
      t1.a2,
      t1.a3
    FROM table1 t1
    GROUP BY t1.a1, t1.a2, t1.a3
  ) t
LEFT JOIN table2 t2 ON t.a1 = t2.b1 AND t.a2 = t2.b2 AND t.a3 = t2.b3
GROUP BY t.a1, t.a2, t.a3

но если в value второй таблицы будет null и этот элемент окажется первым, то это сработает и вернет null На весь массив.
можно было бы вставить проверку длины массива, но к сожалению по непонятным причинам функция ARRAY_LENGTH не работает (т.е. не найдена) ни у меня на pgsql 11 ни в sqlfiddle , как-то странно, а в доке пишут про нее... :??
но есть ARRAY_DIMS и тогда запрос можно переписать так:

SELECT
  t.a1, t.a2, t.a3,
  CASE WHEN
          (ARRAY_DIMS(ARRAY_AGG(t2.value)) = '[1:1]')
      AND ( ARRAY_AGG(t2.value) )[1] IS null
    THEN null ELSE ARRAY_AGG(t2.value)
  END AS arr
FROM (
    SELECT 
      t1.a1,
      t1.a2,
      t1.a3
    FROM table1 t1
    GROUP BY t1.a1, t1.a2, t1.a3
  ) t
LEFT JOIN table2 t2 ON t.a1 = t2.b1 AND t.a2 = t2.b2 AND t.a3 = t2.b3
GROUP BY t.a1, t.a2, t.a3

т.е. собственно ответ:

CASE WHEN
          (ARRAY_DIMS(ARRAY_AGG(t2.value)) = '[1:1]')
      AND ( ARRAY_AGG(t2.value) )[1] IS null
    THEN null ELSE ARRAY_AGG(t2.value)
  END

SELECT 
  t2.b1,
  t2.b2,
  t2.b3,
  ARRAY_AGG(t2.value)
FROM table2 t2
WHERE EXISTS (
  SELECT 1
    FROM table1 t1
    WHERE t1.a1 = t2.b1
      AND t1.a2 = t2.b2
      AND t1.a3 = t2.b3
)
GROUP BY t2.b1, t2.b2, t2.b3

PS:

Подскажите пожалуйста, как в колонке array_agg получить уникальные значения (в виде массива)

вообще-то они не уникальные и я так понимаю вопрос должен был звучать как-то типа:
как из table2 выбрать значения по трем ключам связям с table1 и агрегировать колонку value в "массив"?