Postgresql LEFT JOIN json_agg() 忽略/删除 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24155190/
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
Postgresql LEFT JOIN json_agg() ignore/remove NULL
提问by user3081211
SELECT C.id, C.name, json_agg(E) AS emails FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id;
Postgres 9.3 creates output for example
例如，Postgres 9.3 创建输出
id | name | emails
-----------------------------------------------------------
1 | Ryan | [{"id":3,"user_id":1,"email":"[email protected]"},{"id":4,"user_id":1,"email":"[email protected]"}]
2 | Nick | [null]
As I am using a LEFT JOIN there will be cases where there is no right-table match therefore empty (null) values are substituted for the right-table columns. As a result I am getting [null]as one of the JSON aggregates.
当我使用 LEFT JOIN 时，会出现没有右表匹配的情况，因此空（空）值被替换为右表列。结果，我[null]成为了 JSON 聚合体之一。
How can I ignore/remove nullso I have an empty JSON array []when the right-table column is null?
当右表null列为空[]时，如何忽略/删除以便我有一个空的 JSON 数组？
Cheers!
干杯!
回答by Mike Stankavich
In 9.4 you can use coalesce and an aggregate filter expression.
在 9.4 中，您可以使用合并和聚合过滤器表达式。
SELECT C.id, C.name,
COALESCE(json_agg(E) FILTER (WHERE E.user_id IS NOT NULL), '[]') AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name
ORDER BY C.id;
The filter expression prevents the aggregate from processing the rows that are null because the left join condition is not met, so you end up with a database null instead of the json [null]. Once you have a database null, then you can use coalesce as usual.
过滤器表达式会阻止聚合处理由于不满足左连接条件而为空的行，因此最终会得到数据库空值而不是 json [空值]。一旦你有一个数据库空，那么你可以像往常一样使用coalesce。
http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES
http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES
回答by Roman Pekar
something like this, may be?
这样的事情，可能是？
select
c.id, c.name,
case when count(e) = 0 then '[]' else json_agg(e) end as emails
from contacts as c
left outer join emails as e on c.id = e.user_id
group by c.id
you also can group before join (I'd prefer this version, it's a bit more clear):
你也可以在加入之前分组（我更喜欢这个版本，它更清楚一点）：
select
c.id, c.name,
coalesce(e.emails, '[]') as emails
from contacts as c
left outer join (
select e.user_id, json_agg(e) as emails from emails as e group by e.user_id
) as e on e.user_id = c.id
回答by Jeff
If this is actually a PostgreSQL bug, I hope it's been fixed in 9.4. Very annoying.
如果这实际上是一个 PostgreSQL 错误，我希望它已在 9.4 中得到修复。很烦人。
SELECT C.id, C.name,
COALESCE(NULLIF(json_agg(E)::TEXT, '[null]'), '[]')::JSON AS emails
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id;
I personally don't do the COALESCE bit, just return the NULL. Your call.
我个人不做 COALESCE 位，只返回 NULL。您的来电。
回答by Developer.ca
I used this answer(sorry, I can't seem to link to your username) but I believe I improved it a bit.
我使用了这个答案（抱歉，我似乎无法链接到您的用户名）但我相信我对其进行了一些改进。
For the array version we can
对于数组版本，我们可以
- get rid of the redundant double select
- use json_agginstead of the
array_to_json(array_agg())calls
- 摆脱多余的双选
- 使用json_agg而不是
array_to_json(array_agg())调用
and get this:
并得到这个：
CREATE OR REPLACE FUNCTION public.json_clean_array(p_data JSON)
RETURNS JSON
LANGUAGE SQL IMMUTABLE
AS $$
-- removes elements that are json null (not sql-null) or empty
SELECT json_agg(value)
FROM json_array_elements(p_data)
WHERE value::text <> 'null' AND value::text <> '""';
$$;
For 9.3, for the object version, we can:
对于 9.3，对于对象版本，我们可以：
- get rid of the non-used
WITHclause - get rid of the redundant double select
- 去掉未使用的
WITH子句 - 摆脱多余的双选
and get this:
并得到这个：
CREATE OR REPLACE FUNCTION public.json_clean(p_data JSON)
RETURNS JSON
LANGUAGE SQL IMMUTABLE
AS $$
-- removes elements that are json null (not sql-null) or empty
SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM json_each(p_data)
WHERE value::TEXT <> 'null' AND value::TEXT <> '""';
$$;
For 9.4, we don't have to use the string assembly stuff to build the object, as we can use the newly added json_object_agg
对于 9.4，我们不必使用字符串组装的东西来构建对象，因为我们可以使用新添加的json_object_agg
CREATE OR REPLACE FUNCTION public.json_clean(p_data JSON)
RETURNS JSON
LANGUAGE SQL IMMUTABLE
AS $$
-- removes elements that are json null (not sql-null) or empty
SELECT json_object_agg(key, value)
FROM json_each(p_data)
WHERE value::TEXT <> 'null' AND value::TEXT <> '""';
$$;
回答by maniek
Probably less performant than Roman Pekar's solution, but a bit neater:
性能可能不如 Roman Pekar 的解决方案，但更简洁：
select
c.id, c.name,
array_to_json(array(select email from emails e where e.user_id=c.id))
from contacts c
回答by le-doude
I made my own function for filtering json arrays:
我制作了自己的用于过滤 json 数组的函数：
CREATE OR REPLACE FUNCTION public.json_clean_array(data JSON)
RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
array_to_json(array_agg(value)) :: JSON
FROM (
SELECT
value
FROM json_array_elements(data)
WHERE cast(value AS TEXT) != 'null' AND cast(value AS TEXT) != ''
) t;
$$;
I use it as
我用它作为
select
friend_id as friend,
json_clean_array(array_to_json(array_agg(comment))) as comments
from some_entity_that_might_have_comments
group by friend_id;
of course only works in postgresql 9.3. I also have a similar one for object fields:
当然只适用于 postgresql 9.3。我也有一个类似的对象字段：
CREATE OR REPLACE FUNCTION public.json_clean(data JSON)
RETURNS JSON
LANGUAGE SQL
AS $$
SELECT
('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
FROM (
WITH to_clean AS (
SELECT
*
FROM json_each(data)
)
SELECT
*
FROM json_each(data)
WHERE cast(value AS TEXT) != 'null' AND cast(value AS TEXT) != ''
) t;
$$;
EDIT: You can see a few utils (a few are not originally mine but they were take from other stackoverflow solutions) here at my gist: https://gist.github.com/le-doude/8b0e89d71a32efd21283
编辑：您可以在我的要点中看到一些实用程序（一些不是我最初的，但它们是从其他 stackoverflow 解决方案中获取的）：https: //gist.github.com/le-doude/8b0e89d71a32efd21283
回答by Fabricator
This way works, but there's gotta be a better way :(
这种方法有效，但必须有更好的方法:(
SELECT C.id, C.name,
case when exists (select true from emails where user_id=C.id) then json_agg(E) else '[]' end
FROM contacts C
LEFT JOIN emails E ON C.id = E.user_id
GROUP BY C.id, C.name;
回答by tom
A bit different but might be helpful for others:
有点不同，但可能对其他人有帮助：
If all objects in the array are of same structure (e.g. because you use jsonb_build_objectto create them) you can define a "NULL object with the same structure" to use in array_remove:
如果数组中的所有对象都具有相同的结构（例如因为您jsonb_build_object用来创建它们），您可以定义一个“具有相同结构的 NULL 对象”以用于array_remove：
...
array_remove(
array_agg(jsonb_build_object('att1', column1, 'att2', column2)),
to_jsonb('{"att1":null, "att2":null}'::json)
)
...
