Postgresql LEFT JOIN json_agg() negeer/verwijder NULL

Postgres 9.3 maakt bijvoorbeeld uitvoer

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

Omdat ik een LEFT JOIN gebruik, zullen er gevallen zijn waarin er geen overeenkomst in de rechtertabel is, daarom worden lege (null) waarden vervangen door de rechtertabelkolommen. Als resultaat krijg ik [null]als een van de JSON-aggregaten.

Hoe kan ik nullnegeren/verwijderen zodat ik een lege JSON-array []heb als de rechtertabelkolom null is?

Proost!

Antwoord 1, autoriteit 100%

In 9.4 kun je coalesce en een aggregatiefilterexpressie gebruiken.

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;

De filterexpressie voorkomt dat het aggregaat de rijen verwerkt die null zijn omdat niet aan de linker join-voorwaarde is voldaan, zodat u een database null krijgt in plaats van de json [null]. Als u eenmaal een database-nul heeft, kunt u samenvoegen zoals gewoonlijk gebruiken.

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

Antwoord 2, autoriteit 16%

zoiets zou kunnen zijn?

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

je kunt ook groeperen voordat je lid wordt (ik heb liever deze versie, het is wat duidelijker):

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

Antwoord 3, autoriteit 4%

Als dit een PostgreSQL-bug is, hoop ik dat deze in 9.4 is opgelost. Heel vervelend.

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;

Persoonlijk doe ik het COALESCE-bit niet, maar retourneer gewoon de NULL. Uw oproep.

Antwoord 4, autoriteit 2%

Ik heb dit antwoordgebruikt (sorry, ik kan niet naar je gebruikersnaam linken) maar ik denk dat ik het heb verbeterd het een beetje.

Voor de array-versie kunnen we

1. verwijder de overbodige dubbele selectie
2. gebruik json_aggin plaats van de array_to_json(array_agg())roept

en krijg dit:

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

Voor 9.3, voor de objectversie, kunnen we:

1. verwijder de niet-gebruikte WITH-clausule
2. verwijder de overbodige dubbele selectie

en krijg dit:

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

Voor 9.4 hoeven we de string-assembly-dingen niet te gebruiken om het object te bouwen, omdat we de nieuw toegevoegde 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 <> '""';
$$;

Antwoord 5

Waarschijnlijk minder performant dan de oplossing van Roman Pekar, maar een beetje netter:

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

Antwoord 6

Ik heb mijn eigen functie gemaakt voor het filteren van 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;
$$;

Ik gebruik het als

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;

werkt natuurlijk alleen in postgresql 9.3. Ik heb ook een soortgelijke voor objectvelden:

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: je kunt hier een paar hulpprogramma’s zien (een paar zijn oorspronkelijk niet van mij, maar ze zijn overgenomen van andere stackoverflow-oplossingen):
https://gist.github.com/le-doude/8b0e89d71a32efd21283

Antwoord 7

Een beetje anders, maar kan nuttig zijn voor anderen:

Als alle objecten in de array dezelfde structuur hebben (bijvoorbeeld omdat u jsonb_build_objectgebruikt om ze te maken), kunt u een “NULL-object met dezelfde structuur” definiëren om te gebruiken in array_remove:

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

Antwoord 8

Deze manier werkt, maar er moet een betere manier zijn 🙁

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