postgres functions array_agg array agg sql json postgresql postgresql-9.3

functions - postgresql group by function



Postgresql LEFT JOIN json_agg() ignorar/eliminar NULL (8)

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

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!


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


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


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


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


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.


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


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