Postgresql LEFT JOIN json_agg () игнорировать / удалять NULL

В 9.4 вы можете использовать выражение coalesce и агрегатного фильтра.

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;

Выражение фильтра не позволяет агрегату обрабатывать строки с нулевым значением, потому что условие левого соединения не выполняется, поэтому вы получаете нулевое значение базы данных вместо json [null]. Если у вас есть база данных null, вы можете использовать coalesce как обычно.

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

что-то вроде этого, может быть?

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

вы также можете сгруппировать перед присоединением (я бы предпочел эту версию, она немного понятнее):

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

Если это действительно ошибка 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;

Я лично не использую бит COALESCE, просто возвращаю NULL. Ваш звонок.

Я использовал этот ответ (извините, я не могу связать ваше имя пользователя), но я считаю, что немного улучшил его .

Для версии с массивом мы можем

1. избавиться от избыточного двойного выбора
2. используйте json_agg вместо вызовов array_to_json(array_agg())

и получите это:

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

В версии 9.3 для объектной версии мы можем:

1. избавиться от неиспользованного предложения WITH
2. избавиться от избыточного двойного выбора

и получите это:

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

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

Вероятно, менее производительно, чем решение Романа Пекара, но немного аккуратнее:

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

Я сделал свою функцию для фильтрации 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;
$$;

Я использую это как

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;

конечно работает только в 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;
$$;

РЕДАКТИРОВАТЬ: вы можете увидеть несколько утилит (некоторые из них изначально не мои, но они были взяты из других решений stackoverflow) здесь, в моей сути: https://gist.github.com/le-doude/8b0e89d71a32efd21283

Немного другое, но может быть полезно другим:

Если все объекты в массиве имеют одинаковую структуру (например, потому что вы используете jsonb_build_object для их создания), вы можете определить «NULL объект с той же структурой» для использования в array_remove:

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

Этот способ работает, но должен быть способ получше :(

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;

демонстрация: http://sqlfiddle.com/#!15/ddefb/16