Postgres对于连接表的array_agg返回[null]而不是[] [英] Postgres returns [null] instead of [] for array_agg of join table

问题描述

我正在Postgres中选择一些对象及其标签.模式非常简单，只有三个表:

I'm selecting some objects and their tags in Postgres. The schema is fairly simple, three tables:

对象 id

标记 id |object_id |tag_id

标签 id |标签

我使用 array_agg 将这些标签汇总到一个字段中，从而加入这些表:

I'm joining the tables like this, using array_agg to aggregate the tags into one field:

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将返回以下内容:

However, if the object has no tags, Postgres returns this:

[ null ]

而不是一个空数组.在没有标签的情况下如何返回空数组?我已经仔细检查了是否没有返回空标签.

instead of an an empty array. How can I return an empty array when there are no tags? I have double checked that I don't have a null tag being returned.

汇总文档说:合并功能可以是用于在必要时将零或空数组替换为null".我尝试将 COALESCE(ARRAY_AGG(tags.tag))作为标签，但它仍返回带有null的数组.我尝试使第二个参数变多(例如 COALESCE(ARRAY_AGG(tags.tag)，ARRAY())，但它们都会导致语法错误.

The aggregate docs say "The coalesce function can be used to substitute zero or an empty array for null when necessary". I tried COALESCE(ARRAY_AGG(tags.tag)) as tags but it still returns an array with null. I have tried making the second parameter numerous things (such as COALESCE(ARRAY_AGG(tags.tag), ARRAY()), but they all result in syntax errors.

推荐答案

另一个选项可能是 array_remove(...，NULL)(

Another option might be array_remove(..., NULL) (introduced in 9.3) if tags.tag is NOT NULL (otherwise you might want to keep NULL values in the array, but in that case, you can't distinguish between a single existing NULL tag and a NULL tag due to the 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

如果未找到标签，则返回一个空数组.

If no tags are found, an empty array is returned.

这篇关于Postgres对于连接表的array_agg返回[null]而不是[]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！