array - postgresql group by function

Postgres devuelve[null] en lugar de[] para array_agg de join table

Question

Estoy seleccionando algunos objetos y sus etiquetas en Postgres. El esquema es bastante simple, tres tablas:

id objetos

id | object_id | tag_id etiquetas id | object_id | tag_id id | object_id | tag_id

id | tag etiquetas id | tag id | tag

Me estoy uniendo a las tablas como esta, usando array_agg para agregar las etiquetas en un campo:

SELECT objects.*,
    array_agg(tags.tag) AS tags,
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id

Sin embargo, si el objeto no tiene etiquetas, Postgres devuelve esto:

[ null ]

en lugar de una matriz vacía. ¿Cómo puedo devolver una matriz vacía cuando no hay etiquetas? He comprobado de nuevo que no tengo una etiqueta nula devuelta.

Los documentos agregados dicen que "la función de fusión se puede usar para sustituir cero o una matriz vacía para nulo cuando sea necesario". Intenté COALESCE(ARRAY_AGG(tags.tag)) as tags pero aún así devuelve una matriz con null. He intentado hacer el segundo parámetro muchas cosas (como COALESCE(ARRAY_AGG(tags.tag), ARRAY()) , pero todas dan como resultado errores de sintaxis.

Answer 1

Dado que 9.4 se puede restringir una llamada a la función agregada para continuar solo las filas que coincidan con un cierto criterio: array_agg(tags.tag) filter (where tags.tag is not null)

Answer 2

Descubrí que esto lo hará:

COALESCE(ARRAY_AGG(tags.tag), ARRAY[]::TEXT[])

... asumiendo que tags.tag es un tipo de texto.

No estoy seguro de si tal vez esto no funcionaría en versiones anteriores de Postgres, pero lo estoy usando en ver. 9.6 y parece estar funcionando y es menos engorroso que la solución CASE WHEN x IS NULL... GROUP BY... proporcionada anteriormente.

Answer 3

La documentación dice que se devuelve una matriz que contiene NULL . Si quieres convertir eso en una matriz vacía, entonces necesitas hacer un poco de magia menor:

SELECT objects.id,
    CASE WHEN length((array_agg(tags.tag))[1]) > 0
    THEN array_agg(tags.tag) 
    ELSE ARRAY[]::text[] END AS tags
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id
GROUP BY 1;

Esto supone que las etiquetas son de tipo de text (o cualquiera de sus variantes); modificar el elenco según sea necesario.

El truco aquí es que el primer (y único) elemento en una matriz [NULL] tiene una longitud de 0, por lo que si se devuelven datos de las tags se devuelve el agregado; de lo contrario, cree una matriz vacía del tipo correcto.

Por cierto, la declaración en la documentación sobre el uso de coalesce() es un poco desagradable: lo que se quiere decir es que si no quiere NULL como resultado, puede usar coalesce() para convertirlo en un 0 o alguna otra salida de su elegir Pero debe aplicar eso a los elementos de la matriz en lugar de la matriz, que, en su caso, no proporcionaría una solución.

Answer 4

Los documentos dicen que cuando está agregando cero filas, entonces obtiene un valor nulo, y la nota sobre el uso de COALESCE está abordando este caso específico.

Esto no se aplica a su consulta, debido a la forma en que se comporta una LEFT JOIN : cuando encuentra cero filas coincidentes, devuelve una fila, llena de nulos (y el agregado de una fila nula es una matriz con un elemento nulo).

Puede tener la tentación de reemplazar a ciegas [NULL] con [] en la salida, pero luego pierde la capacidad de distinguir entre los objetos sin etiquetas y los objetos etiquetados donde tags.tag es nulo . La lógica de la aplicación y / o las restricciones de integridad pueden no permitir este segundo caso, pero esa es una razón más para no suprimir una etiqueta nula si logra colarse.

Puede identificar un objeto sin etiquetas (o, en general, saber cuándo una LEFT JOIN no encontró coincidencias) al verificar si el campo en el otro lado de la condición de unión es nulo. Así que en tu caso, simplemente reemplaza

array_agg(tags.tag)

con

CASE
  WHEN taggings.object_id IS NULL
  THEN ARRAY[]::text[]
  ELSE array_agg(tags.tag)
END

Answer 5

Otra opción podría ser array_remove(..., NULL) ( introducida en 9.3 ) si tags.tag NOT NULL es NOT NULL (de lo contrario, es posible que desee mantener los valores NULL en la matriz, pero en ese caso, no puede distinguir entre una sola etiqueta NULL existente y una etiqueta NULL debido a LEFT JOIN ):

SELECT objects.*,
     array_remove(array_agg(tags.tag), NULL) AS tags,
FROM objects
LEFT JOIN taggings ON objects.id = taggings.object_id
LEFT JOIN tags ON tags.id = taggings.tag_id

Si no se encuentran etiquetas, se devuelve una matriz vacía.