Postgreql LEFT JOIN json_agg()忽略/移除 NULL

我使用的是 LEFT JOIN，在某些情况下，不存在右表匹配，因此空(null)值被替换为右表列。因此，我得到了 [null]作为 JSON 聚合之一。

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创建输出

  id  |  name  |  emails
-----------------------------------------------------------
1  |  Ryan  |  [{"id":3,"user_id":1,"email":"hello@world.com"},{"id":4,"user_id":1,"email":"again@awesome.com"}]
2  |  Nick  |  [null]

当右表列为 null 时，我如何忽略/删除 null以便拥有一个空的 JSON 数组 []？

42533 次浏览
小开

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;

demo: http://sqlfiddle.com/#!15/ddefb/16

小开

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

sql fiddle demo

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

sql fiddle demo

小开

Probably less performant than Roman Pekar's solution, but a bit neater:

select
c.id, c.name,
array_to_json(array(select email from emails e where e.user_id=c.id))
from contacts c
小开

I made my own function for filtering json arrays:

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:

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

小开

If this is actually a PostgreSQL bug, I hope it's been fixed in 9.4. Very annoying.

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.

小开

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

  1. get rid of the redundant double select
  2. use json_agg instead of the array_to_json(array_agg()) calls

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:

  1. get rid of the non-used WITH clause
  2. get rid of the redundant double select

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

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 <> '""';
$$;
小开

In 9.4 you can use coalesce and an aggregate filter expression.

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.

http://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES

小开

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_object to create them) you can define a "NULL object with the same structure" to use in array_remove:

...
array_remove(
array_agg(jsonb_build_object('att1', column1, 'att2', column2)),
to_jsonb('{"att1":null, "att2":null}'::json)
)
...
小开

At the time this question was asked, the following example might not have been as efficient of a choice, due to the nature of how the email_list would basically not limit itself based on the outer query, but newer versions of postgres handle this much better (also, I'd recommend jsonb over json)

WITH email_list (user_id, emails) as (
SELECT user_id, json_agg(emails) FROM emails GROUP BY user_id
)
SELECT C.id, C.name, COALESCE(E.emails, '[]'::json) as emails
FROM contacts C LEFT JOIN email_list E ON C.id = E.user_id;

The COALESCE is only needed if you actually do want to have an empty array, otherwise the entire value would be null, which can be preferable output in some languages.


提交答案