functions - postgresql group by function

Postgresql LEFT JOIN json_agg() ignorar/eliminar NULL

Question

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 crea salida por ejemplo

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

Cuando estoy usando un LEFT JOIN, habrá casos en los que no haya una coincidencia de la tabla derecha, por lo que los valores vacíos (nulos) se sustituyen por las columnas de la tabla derecha. Como resultado, obtengo [null] como uno de los agregados JSON.

¿Cómo puedo ignorar / eliminar null para tener una matriz JSON vacía [] cuando la columna de la tabla derecha es nula?

¡Aclamaciones!

Answer 1

Algo como esto, puede ser?

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

demo de sql fiddle

También puedes agrupar antes de unirte (preferiría esta versión, es un poco más claro):

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

demo de sql fiddle

Answer 2

En 9.4 puede usar coalescencia y una expresión de filtro agregada.

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;

La expresión de filtro impide que el agregado procese las filas que son nulas porque no se cumple la condición de unión izquierda, por lo que terminará con una base de datos nula en lugar de json [null]. Una vez que tenga una base de datos nula, puede usar la fusión como de costumbre.

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

Answer 3

Esta manera funciona, pero tiene que haber una mejor manera :(

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;

demostración: http://sqlfiddle.com/#!15/ddefb/16

Answer 4

Hice mi propia función para filtrar 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;
$$;

Lo uso como

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;

Por supuesto solo funciona en postgresql 9.3. También tengo una similar para campos de objeto:

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

EDITAR: Puede ver algunas utilidades (algunas no son originalmente mías, pero fueron tomadas de otras soluciones de ) aquí en mi esencia: https://gist.github.com/le-doude/8b0e89d71a32efd21283

Answer 5

Probablemente sea menos eficaz que la solución de Roman Pekar, pero un poco más ordenada:

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

Answer 6

Si esto es realmente un error de PostgreSQL, espero que se haya corregido en 9.4. Muy molesto.

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;

Personalmente no hago el bit COALESCE, solo devuelvo el valor NULL. Tu llamada.

Answer 7

Un poco diferente, pero podría ser útil para otros:

Si todos los objetos de la matriz tienen la misma estructura (por ejemplo, porque utiliza jsonb_build_object para crearlos), puede definir un "objeto NULL con la misma estructura" para usar en array_remove :

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

Answer 8

Utilicé esta respuesta (lo siento, parece que no puedo enlazar con tu nombre de usuario) pero creo que la mejoré un poco.

Para la versión de matriz podemos

1. deshacerse de la doble selección redundante
2. use json_agg lugar de las array_to_json(array_agg())

y obtén esto:

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

Para 9.3, para la versión del objeto, podemos:

1. deshacerse de la cláusula WITH no utilizada
2. deshacerse de la doble selección redundante

y obtén esto:

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

Para 9.4, no tenemos que usar el conjunto de cadenas para construir el objeto, ya que podemos usar el json_object_agg recién agregado

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