DISTINCT ON в совокупной функции в postgres

Ответ 1

Всякий раз, когда у вас есть центральная таблица и вы хотите присоединиться к ней во многие строки таблицы A, а также соединить ее со многими строками в таблице B, вы сталкиваетесь с этими проблемами дублирования строк. Это может особенно сбросить функции агрегации, такие как COUNT и SUM, если вы не будете осторожны! Поэтому я думаю, что вам нужно отдельно создавать свои метки для каждой фотографии и комментарии для каждой фотографии, а затем объединить их:

WITH tags AS (
  SELECT  photo.photo_id, json_agg(row_to_json(tag.*)) AS tags
  FROM    photo
  LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id
  LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
  GROUP BY photo.photo_id
),
comments AS (
  SELECT  photo.photo_id, json_agg(row_to_json(comment.*)) AS comments
  FROM    photo
  LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
  GROUP BY photo.photo_id
)
SELECT  COALESCE(tags.photo_id, comments.photo_id) AS photo_id,
        tags.tags,
        comments.comments
FROM    tags
FULL OUTER JOIN comments
ON      tags.photo_id = comments.photo_id

EDIT: Если вы действительно хотите объединить все вместе без CTE, похоже, что он дает правильные результаты:

SELECT  photo.photo_id,
        to_json(array_agg(DISTINCT tag.*)) AS tags,
        to_json(array_agg(DISTINCT comment.*)) AS comments
FROM    photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id

Ответ 2

Самая дешевая и простая операция DISTINCT - это не умножать строки в "перекрестном соединении прокси". Агрегируйте сначала, затем присоединяйтесь. Увидеть:

• Два SQL LEFT JOINS дают неверный результат

Лучше всего возвращать несколько выбранных строк

Предполагая, что вы на самом деле не хотите, чтобы получить всю таблицу, но только один или несколько выбранных фотографий в то время, с агрегированным деталей, самый элегантный и, вероятно, самый быстрый путь с LATERAL подзапросов:

SELECT *
FROM   photo p
CROSS  JOIN LATERAL (
   SELECT json_agg(c) AS comments
   FROM   comment c
   WHERE  photo_id = p.photo_id
   ) c1
CROSS  JOIN LATERAL (
   SELECT json_agg(t) AS tags
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   WHERE  pt.photo_id = p.photo_id
   ) t
WHERE  p.photo_id = 2;  -- arbitrary selection

Это возвращает целые строки из comment и tag, сгруппированные в массивы JSON отдельно. Строки не являются умножениями, как в вашей попытке, но они настолько же "различны", как и в ваших базовых таблицах.

Чтобы дополнительно сложить дубликаты в базе данных, см. Ниже.

Заметки:

• LATERAL и json_agg() требуют Postgres 9.3 или более поздней версии.

• json_agg(c) - сокращение от json_agg(c.*).

• Нам не нужно LEFT JOIN потому что агрегатная функция, такая как json_agg() всегда возвращает строку.

Как правило, вам нужно только подмножество столбцов - по крайней мере, исключая избыточный photo_id:

SELECT *
FROM   photo p
CROSS  JOIN LATERAL (
   SELECT json_agg(json_build_object('comment_id', comment_id
                                   , 'comment', comment)) AS comments
   FROM   comment
   WHERE  photo_id = p.photo_id
   ) c
CROSS  JOIN LATERAL (
   SELECT json_agg(t) AS tags
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   WHERE  pt.photo_id = p.photo_id
   ) t
WHERE  p.photo_id = 2;

json_build_object() был представлен в Postgres 9.4. Раньше было громоздко в старых версиях, потому что конструктор ROW не сохраняет имена столбцов. Но есть общие обходные пути:

• Возврат в виде массива объектов JSON в SQL (Postgres)

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

Лучше всего вернуть всю таблицу

Чтобы вернуть все строки, это более эффективно:

SELECT p.*
     , COALESCE(c1.comments, '[]') AS comments
     , COALESCE(t.tags, '[]') AS tags
FROM   photo p
LEFT   JOIN (
   SELECT photo_id
        , json_agg(json_build_object('comment_id', comment_id
                                   , 'comment', comment)) AS comments
   FROM   comment c
   GROUP  BY 1
   ) c1 USING (photo_id)
LEFT  JOIN LATERAL (
   SELECT photo_id , json_agg(t) AS tags
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   GROUP  BY 1
   ) t USING (photo_id);

Как только мы получим достаточно строк, это станет дешевле, чем LATERAL подзапросов. Работает на Postgres 9. 3+.

Обратите внимание на предложение USING в условии соединения. Таким образом, мы можем удобно использовать SELECT * во внешнем запросе, не получая дубликаты столбцов для photo_id. Я не использовал SELECT * здесь, потому что ваш удаленный ответ указывает на то, что вы хотите пустые массивы JSON вместо NULL без тегов/комментариев.

Также удалите существующие дубликаты в базовых таблицах

Вы не можете просто json_agg(DISTINCT json_build_object(...)) потому что нет оператора равенства для типа данных json. Увидеть:

• Как запросить столбец JSON для пустых объектов?

Есть различные лучшие способы:

SELECT *
FROM   photo p
CROSS  JOIN LATERAL (
   SELECT json_agg(to_json(c1.comment)) AS comments1
        , json_agg(json_build_object('comment', c1.comment)) AS comments2
        , json_agg(to_json(c1)) AS comments3
   FROM  (
      SELECT DISTINCT c.comment  -- folding dupes here
      FROM   comment c
      WHERE  c.photo_id = p.photo_id
   -- ORDER  BY comment --  any particular order?
      ) c1
   ) c2
CROSS  JOIN LATERAL (
   SELECT jsonb_agg(DISTINCT t) AS tags  -- demonstrating jsonb_agg
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   WHERE  pt.photo_id = p.photo_id
   ) t
WHERE  p.photo_id = 2;

Демонстрация 4 различных методов в comments1, comments2, comments3 (избыточно) и tags.

дБ <> скрипка здесь
Старая скрипта SQL, исправленная в Postgres 9.3
Старая SQL скрипка для Postgres 9.6

Ответ 3

Как указано в комментариях, json_agg не сериализует строку как объект, а строит массив JSON значений, которые вы передаете. Вам понадобится row_to_json, чтобы превратить вашу строку в объект JSON, а затем json_agg выполнить агрегацию в массив:

SELECT json_agg(DISTINCT row_to_json(comment)) as tags
FROM
    photo
    LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
    LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
    LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id