CODEWITHSUNDEEP
postgresql
user3081211
user3081211

Reputation: 1341

Postgresql LEFT JOIN json_agg() ignore/remove NULL

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.

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

  id  |  name  |  emails
-----------------------------------------------------------
   1  |  Ryan  |  [{"id":3,"user_id":1,"email":"[email protected]"},{"id":4,"user_id":1,"email":"[email protected]"}]
   2  |  Nick  |  [null]

How can I ignore/remove null so I have an empty JSON array [] when the right-table column is null?

Upvotes: 121

Views: 65113

Answers (10)

Nickolay Alexandrov
Nickolay Alexandrov

Reputation: 31

In 16+ you can use json_agg_strict or jsonb_agg_strict functions.

Upvotes: 3

CStroliaDavis
CStroliaDavis

Reputation: 402

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.

Upvotes: 0

tom
tom

Reputation: 2357

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)
)
...

Upvotes: 3

Mike Stankavich
Mike Stankavich

Reputation: 3118

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

Upvotes: 210

roman
roman

Reputation: 117560

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

Upvotes: 33

Developer.ca
Developer.ca

Reputation: 41

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

Upvotes: 4

Jeff
Jeff

Reputation: 593

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.

Upvotes: 10

le-doude
le-doude

Reputation: 3367

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

Upvotes: 1

maniek
maniek

Reputation: 7307

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

Upvotes: 2

Fabricator
Fabricator

Reputation: 12782

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

Upvotes: 0

Related Questions