postgresql Postgres 为连接表的 array_agg 返回 [null] 而不是 []
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31108946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres returns [null] instead of [] for array_agg of join table
提问by Andy Ray
I'm selecting some objects and their tags in Postgres. The schema is fairly simple, three tables:
我正在 Postgres 中选择一些对象及其标签。架构相当简单，三个表：
objectsid
对象id
taggingsid | object_id | tag_id
标签id | object_id | tag_id
tagsid | tag
标签id | tag
I'm joining the tables like this, using array_aggto aggregate the tags into one field:
我正在加入这样的表格，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
However, if the object has no tags, Postgres returns this:
但是，如果对象没有标签，Postgres 会返回：
[ 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.
而不是一个空数组。没有标签时如何返回空数组？我已经仔细检查过我没有返回空标签。
The aggregate docssay "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 tagsbut 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.
所述骨料文档说“必要时聚结功能可以用来替代零或空数组空”。我试过了，COALESCE(ARRAY_AGG(tags.tag)) as tags但它仍然返回一个空数组。我曾尝试将第二个参数设置为很多东西（例如COALESCE(ARRAY_AGG(tags.tag), ARRAY())，但它们都会导致语法错误。
采纳答案by Thomas Perl
Another option might be array_remove(..., NULL)(introduced in 9.3) if tags.tagis NOT NULL(otherwise you might want to keep NULLvalues in the array, but in that case, you can't distinguish between a single existing NULLtag and a NULLtag due to the LEFT JOIN):
另一个选项可能是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
If no tags are found, an empty array is returned.
如果没有找到标签，则返回一个空数组。
回答by Alexey Bashtanov
Since 9.4 one can restrict an aggregate function call to proceed only rows that match a certain criterion: array_agg(tags.tag) filter (where tags.tag is not null)
从 9.4 开始，可以限制聚合函数调用只处理符合特定条件的行： array_agg(tags.tag) filter (where tags.tag is not null)
回答by Nick Barnes
The docs say that when you are aggregating zero rows, then you get a null value, and the note about using COALESCEis addressing this specific case.
文档说，当您聚合零行时，您会得到一个空值，有关使用的说明COALESCE是针对这种特定情况的。
This does not apply to your query, because of the way a LEFT JOINbehaves - when it finds zeromatching rows, it returns onerow, filled with nulls (and the aggregate of one null row is an array with one null element).
这并不适用于您的查询，因为方式LEFT JOIN的行为-当它发现零点匹配的行，它返回一个排，用空值填充（和一个空行的聚集物是与一个空元素的数组）。
You might be tempted to blindly replace [NULL]with []in the output, but then you lose the ability to distiguish between objects with no tagsand tagged objects where tags.tagis null. Your application logic and/or integrity constraints may not allow this second case, but that's all the more reason not to suppress a null tag if it does manage to sneak in.
你也许会盲目地更换[NULL]与[]输出，但你失去的能力distiguish没有标签的物体和标签的对象，其中tags.tag为空。您的应用程序逻辑和/或完整性约束可能不允许出现第二种情况，但如果它确实设法潜入，那就更有理由不要抑制空标记。
You can identify an object with no tags (or in general, tell when a LEFT JOINfound no matches) by checking whether the field on the other side of the join condition is null. So in your case, just replace
您可以LEFT JOIN通过检查连接条件另一侧的字段是否为空来识别没有标签的对象（或者一般来说，判断何时没有找到匹配项）。所以在你的情况下，只需更换
array_agg(tags.tag)
with
和
CASE
WHEN taggings.object_id IS NULL
THEN ARRAY[]::text[]
ELSE array_agg(tags.tag)
END
回答by Patrick
The documentation says that an array containing NULLis returned. If you want to convert that to an empty array, then you need to do some minor magic:
文档说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;
This assumes that the tags are of texttype (or any of its variants); modify the cast as required.
这假设标签是text类型（或其任何变体）；根据需要修改演员表。
The trick here is that the first (and only) element in a [NULL]array has a length of 0, so if any data is returned from tagsyou return the aggregate, otherwise construct an empty array of the right type.
这里的技巧是[NULL]数组中第一个（也是唯一一个）元素的长度为 0，因此如果从tags您返回任何数据，则返回聚合，否则构造一个正确类型的空数组。
Incidentally, the statement in the documentation about using coalesce()is a bit crummy: what is meant is that if you do not want NULLas a result, you can use coalesce()to turn that into a 0or some other output of your choosing. But you need to apply that to the array elementsinstead of the array, which, in your case, would not provide a solution.
顺便说一句，文档中关于 using 的声明coalesce()有点糟糕：这意味着如果您不想要NULL结果，您可以使用coalesce()将其转换0为您选择的一个或其他一些输出。但是您需要将其应用于数组元素而不是数组，在您的情况下，这不会提供解决方案。
回答by Bart Hofland
Perhaps this answer is a little late, but I wanted to share with you that another querying strategy is possible as well: performing the aggregation in a a separate (common) table expression.
也许这个答案有点晚了，但我想与您分享另一种查询策略也是可能的：在单独的（公共）表表达式中执行聚合。
WITH cte_tags AS (
SELECT
taggings.object_id,
array_agg(tags.tag) AS tags
FROM
taggings
INNER JOIN tags ON tags.id = taggings.tag_id
GROUP BY
taggings.object_id
)
SELECT
objects.*,
cte_tags.tags
FROM
objects
LEFT JOIN cte_tags ON cte_tags.object_id = objects.id
Instead of an array with a single element of NULL, you will now get NULL instead of an array.
您现在将获得 NULL 而不是数组，而不是具有单个 NULL 元素的数组。
If you really want an empty array instead of NULL in your results, you can use the COALESCEfunction...:
如果你真的想要一个空数组而不是 NULL 在你的结果中，你可以使用COALESCE函数...：
WITH cte_tags AS (
SELECT
taggings.object_id,
array_agg(tags.tag) AS tags
FROM
taggings
INNER JOIN tags ON tags.id = taggings.tag_id
GROUP BY
taggings.object_id
)
SELECT
objects.*,
COALESCE(cte_tags.tags, '{}') AS tags
FROM
objects
LEFT JOIN cte_tags ON cte_tags.object_id = objects.id
...or use array-to-array concatenation:
...或使用数组到数组连接：
WITH cte_tags AS (
SELECT
taggings.object_id,
array_agg(tags.tag) AS tags
FROM
taggings
INNER JOIN tags ON tags.id = taggings.tag_id
GROUP BY
taggings.object_id
)
SELECT
objects.*,
cte_tags.tags || '{}' AS tags
FROM
objects
LEFT JOIN cte_tags ON cte_tags.object_id = objects.id
