Postgres возвращает [null] вместо [] для array_agg таблицы join

Я выбираю некоторые объекты и их теги в Postgres. Схема довольно проста, три таблицы:

объекты id

теги id | object_id | tag_id

теги id | tag

Я присоединяюсь к таблицам следующим образом, используя array_agg для агрегирования тегов в одном поле:

SELECT objects.*,
    array_agg(tags.tag) AS tags,
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id

Однако, если объект не имеет тегов, Postgres возвращает это:

[ null ]

вместо пустого массива. Как я могу вернуть пустой массив, когда нет тегов? Я дважды проверял, что у меня нет возвращаемого нулевого тега.

aggregate docs говорят: "Функция coalesce может использоваться для замены нуля или пустого массива для нулевого значения, когда это необходимо". Я попробовал COALESCE(ARRAY_AGG(tags.tag)) as tags, но он все равно возвращает массив с нулевым значением. Я попытался сделать второй параметр многочисленными вещами (например, COALESCE(ARRAY_AGG(tags.tag), ARRAY()), но все они приводят к синтаксическим ошибкам.

Ответ 1

Другим вариантом может быть array_remove(..., NULL) (представленный в 9.3), если tags.tag - NOT NULL (в противном случае вы можете захотеть сохранить NULL в массиве, но в этом случае вы не можете различать один существующий тег NULL и тег NULL из-за LEFT JOIN):

SELECT objects.*,
     array_remove(array_agg(tags.tag), NULL) AS tags,
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id

Если теги не найдены, возвращается пустой массив.

Ответ 2

Документы говорят, что когда вы объединяете нулевые строки, вы получаете нулевое значение, а примечание об использовании COALESCE обращается к этому конкретному случаю.

Это не относится к вашему запросу из-за того, как ведет себя LEFT JOIN - когда он находит нулевые соответствующие строки, он возвращает одну строку, заполненную нулями (и совокупность одной нулевой строки представляет собой массив с одним нулевым элемент).

Возможно, у вас возникнет соблазн вслепую заменить [NULL] на [] на выходе, но тогда вы потеряете способность различать объекты без тегов и с тегами, где tags.tag null. Ограничения вашей логики приложения и/или целостности могут не разрешить этот второй случай, но тем более причина не подавлять нулевой тег, если он действительно успевает проникнуть.

Вы можете идентифицировать объект без тегов (или вообще сказать, когда LEFT JOIN не найдено совпадений), проверяя, является ли поле с другой стороны условия соединения нулевым. Поэтому в вашем случае просто замените

array_agg(tags.tag)

CASE
  WHEN taggings.object_id IS NULL
  THEN ARRAY[]::text[]
  ELSE array_agg(tags.tag)
END

Ответ 3

Начиная с 9.4, можно ограничить вызов агрегатной функции для выполнения только строк, которые соответствуют определенному критерию: array_agg(tags.tag) filter (where tags.tag is not null)

Ответ 4

В документации указано, что возвращается массив, содержащий NULL. Если вы хотите преобразовать это в пустой массив, вам нужно сделать небольшую магию:

SELECT objects.id,
    CASE WHEN length((array_agg(tags.tag))[1]) > 0
    THEN array_agg(tags.tag) 
    ELSE ARRAY[]::text[] END AS tags
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id
GROUP BY 1;

Это предполагает, что теги имеют тип text (или любой из его вариантов); при необходимости измените приведение.

Трюк здесь в том, что первый (и единственный) элемент в массиве [NULL] имеет длину 0, поэтому, если какие-либо данные возвращаются из tags, вы возвращаете агрегат, в противном случае строите пустой массив справа тип.

Кстати, утверждение в документации об использовании coalesce() немного крамова: подразумевается, что если вы не хотите NULL в результате, вы можете использовать coalesce(), чтобы превратить это в 0 или какой-либо другой выход по вашему выбору. Но вам нужно применить это к элементам массива, а не к массиву, который в вашем случае не будет предоставлять решение.

Ответ 5

Я узнал, что это сделает:

COALESCE(ARRAY_AGG(tags.tag), ARRAY[]::TEXT[])

... предполагая, что tags.tag является текстовым типом.

Не уверен, может быть, это не сработает в более старых версиях Postgres, но я использую его в версии. 9.6 и, похоже, работает и менее громоздко, чем ранее предлагаемое решение CASE WHEN x IS NULL... GROUP BY....