Postgres: уникальные теги в json_agg и использование DISTINCT ON

Быстрый ответ

Для применения DISTINCT ON вместе с агрегатной функцией использование подзапроса или Общего Табличного Выражения (CTE) будет наиболее подходящим решением:

WITH DistinctRows AS (
  SELECT DISTINCT ON (column_A) *
  FROM your_table
  ORDER BY column_A, column_B
)
SELECT SUM(column_C) FROM DistinctRows;

В этом примере применён запрос WITH, выбирающий уникальные значения столбца "column_A" из таблицы "your_table", после чего происходит сложение значений столбца column_C по всем уникальным записям.

Работа с множественными строками

Управление несколькими строками в запросе может быть также сложным, как попытка сделать селфи со стаей кошек. При такой работе требуется обеспечить уникальность каждой строки и сгруппировать их в один запрос. Для этого весьма полезными будут подзапросы LATERAL. Их использование может выглядеть так:

SELECT p.photo_id, COALESCE(tag_agg.tags, '[]') as tags, COALESCE(comment_agg.comments, '[]') as comments
FROM photos p
LEFT JOIN LATERAL (  
  SELECT JSON_AGG(DISTINCT t.name) as tags
  FROM tags t
  WHERE p.photo_id = t.photo_id
) tag_agg ON TRUE
LEFT JOIN LATERAL (
  SELECT JSON_AGG(DISTINCT c.text) as comments
  FROM comments c
  WHERE p.photo_id = c.photo_id
) comment_agg ON TRUE;

Ключевое слово LATERAL позволяет подзапросам ссылаться на столбцы присутствующие в основном запросе, что делает их невероятно гибким и мощным инструментом.

Один шар на ведро (Визуализация)

Представьте себе столбцы как ведра, а строки как цветные шарики:

Ведра (⚙️): [Марка, Год, Модель] Шарики (🔵): [Экземпляры автомобилей]

Вы хотите выбрать только ОДНОГО шарика (самую новую модель) для каждой марки автомобиля, используя при этом DISTINCT ON и MAX(Model):

SELECT DISTINCT ON (Brand) Brand, Year, MAX(Model)
FROM Cars
GROUP BY Brand, Year;

В итоге для каждой марки будет отобрана только самая новая модель.

Рафинированные JSON-ответы

Если вам нравится, когда ваш обед подается особым способом (как Гарфилду), json_build_object создаст объект JSON с изысканным блюдом:

SELECT photo_id, 
       COALESCE(json_agg(DISTINCT tag.tag_name), '[]') AS tags,
       COALESCE(
         (SELECT json_agg(DISTINCT comment)
          FROM (
            SELECT comment_id, text
            FROM comments WHERE photo_id = p.photo_id
          ) comment
         ), '[]'
       ) AS comments
FROM photos p
LEFT JOIN tags t USING (photo_id)
GROUP BY photo_id;

Этот запрос создаст очаровательный объект JSON, который придется по вкусу даже самой разборчивой кошке. Использование COALESCE помогает избежать появления null-значений в массиве JSON.

Управление неполадками как босс-кошка: избегайте распространённых ошибок

• Чтобы избежать появления дубликатов, убедитесь, что все уникальные поля присутствуют в GROUP BY.
• Для улучшения читаемости кода активно применяйте CTE.
• Обеспечьте оптимальную сложность запросов, используя индексацию и стратегии улучшения производительности.
• Обрабатывайте null значения с помощью COALESCE, чтобы обеспечить корректное представление JSON-массивов.

Полезные материалы

1. PostgreSQL: Документация: 16: SELECT — официальная документация PostgreSQL по SELECT и DISTINCT ON.
2. PostgreSQL DISTINCT ON с другим ORDER BY – Stack Overflow — обсуждение в сообществе по использованию DISTINCT ON с различными вариантами ORDER BY.
3. postgresql – Выбор колонок внутри json_agg – Database Administrators Stack Exchange — дискуссии по сочетанию агрегатных функций с DISTINCT ON.
4. Практические SQL-запросы для клаузы DISTINCT ON — руководство по оптимизации запросов для выбора уникальных строк с использованием DISTINCT ON.