sql - sintaxis - DISTINCT ON en una función agregada en postgres

sintaxis distinct postgresql

Question

Para mi problema, tenemos un esquema por el cual una foto tiene muchas etiquetas y también muchos comentarios. Entonces, si tengo una consulta donde quiero todos los comentarios y etiquetas, multiplicará las filas juntas. Entonces, si una foto tiene 2 etiquetas y 13 comentarios, obtengo 26 filas para esa foto:

SELECT
tag.name,
comment.comment_id
FROM
photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id

Eso está bien para la mayoría de las cosas, pero significa que si GROUP BY y luego json_agg(tag.*) , Obtengo 13 copias de la primera etiqueta, y 13 copias de la segunda etiqueta.

SELECT json_agg(tag.name) as tags
FROM
photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id

En cambio, quiero una matriz que solo sea ''suburbana'' y ''ciudad'', como esta:

 [
{"tag_id":1,"name":"suburban"},
{"tag_id":2,"name":"city"}
]

Podría json_agg(DISTINCT tag.name) , pero esto solo hará una matriz de nombres de etiquetas, cuando quiera que toda la fila sea json. Me gustaría json_agg(DISTINCT ON(tag.name) tag.*) , Pero aparentemente no es un SQL válido.

Entonces, ¿cómo puedo simular DISTINCT ON dentro de una función agregada en Postgres?

Answer 1

Como se indica en los comentarios, json_agg no serializa una fila como un objeto, sino que crea una matriz JSON de los valores a los que se le pasa . Necesitará row_to_json para convertir su fila en un objeto JSON, y luego json_agg para realizar la agregación a una matriz:

SELECT json_agg(DISTINCT row_to_json(comment)) as tags
FROM
photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id

Answer 2

La operación DISTINCT más barata y simple es ... no multiplicar filas en una "unión cruzada de proxy" en primer lugar. Agregue primero, luego únase. Ver:

• Dos SQL LEFT JOINS producen un resultado incorrecto

Mejor para devolver algunas filas seleccionadas

Suponiendo que en realidad no desea recuperar la tabla completa, sino solo una o unas pocas fotos seleccionadas a la vez, con detalles agregados, la forma más elegante y probablemente más rápida es con las subconsultas LATERALES :

SELECT *
FROM photo p
CROSS JOIN LATERAL (
SELECT json_agg(c) AS comments
FROM comment c
WHERE photo_id = p.photo_id
) c
CROSS JOIN LATERAL (
SELECT json_agg(t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
WHERE pt.photo_id = p.photo_id
) t
WHERE p.photo_id = 2; -- arbitrary selection

Esto debería darte exactamente lo que has estado pidiendo. DISTINCT filas completas de DISTINCT del comment y la tag se agregan a los objetos JSON. LATERAL y json_agg() requieren Postgres 9.3+ .

• json_agg(c) es la abreviatura de json_agg(c.*) .

• No necesitamos LEFT JOIN porque una función agregada como json_agg() siempre devuelve una fila.

Pero supongo que en realidad estás buscando :

SELECT *
FROM photo p
CROSS JOIN LATERAL (
SELECT json_agg(json_build_object(''comment_id'', comment_id
, ''comment'', comment)) AS comments
FROM comment
WHERE photo_id = p.photo_id
) c
CROSS JOIN LATERAL (
SELECT json_agg(t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
WHERE pt.photo_id = p.photo_id
) t
WHERE p.photo_id = 2;

Normalmente, solo querría un subconjunto de columnas ( al menos excluyendo el photo_id redundante). Solía ​​ser complicado en versiones anteriores porque un constructor ROW Postgres no conserva los nombres de columna. json_build_object() se introdujo en Postgres 9.4+ , pero hay soluciones para versiones anteriores:

• Devolver como matriz de objetos JSON en SQL (Postgres)

Esto también permite elegir los nombres de clave JSON libremente, no tiene que atenerse a los nombres de columna.

Lo mejor para devolver toda la mesa.

Para devolver todas las filas, esto es más eficiente:

SELECT p.*
, COALESCE(c.comments, ''[]'') AS comments
, COALESCE(t.tags, ''[]'') AS tags
FROM photo p
LEFT JOIN (
SELECT photo_id
, json_agg(json_build_object(''comment_id'', comment_id
, ''comment'', comment)) AS comments
FROM comment c
GROUP BY 1
) c USING (photo_id)
LEFT JOIN LATERAL (
SELECT photo_id , json_agg(t) AS tags
FROM photo_tag pt
JOIN tag t USING (tag_id)
GROUP BY 1
) t USING (photo_id);

Una vez que recuperamos suficientes filas, esto se vuelve más barato que las subconsultas LATERALES. Trabaja para Postgres 9.3+ .

Observe la cláusula USING en la condición de unión. De esta manera, podemos usar convenientemente SELECT * en la consulta externa sin obtener columnas duplicadas para photo_id . No lo usé aquí porque su respuesta eliminada parece indicar que desea matrices JSON vacías en lugar de NULL para ninguna etiqueta / sin comentarios.

Fiddle de SQL demostrando todo, backpatched a Postgres 9.3. El violín de SQL para Postgres 9.6.

Answer 3

Siempre que tenga una tabla central y desee unirla a la izquierda a muchas filas en la tabla A y también a la izquierda a muchas filas en la tabla B, obtendrá estos problemas de duplicación de filas. ¡Especialmente puede eliminar funciones agregadas como COUNT y SUM si no tiene cuidado! Así que creo que necesitas construir tus etiquetas para cada foto y comentarios para cada foto por separado, y luego unirlas:

WITH tags AS (
SELECT photo.photo_id, json_agg(row_to_json(tag.*)) AS tags
FROM photo
LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id
),
comments AS (
SELECT photo.photo_id, json_agg(row_to_json(comment.*)) AS comments
FROM photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
GROUP BY photo.photo_id
)
SELECT COALESCE(tags.photo_id, comments.photo_id) AS photo_id,
tags.tags,
comments.comments
FROM tags
FULL OUTER JOIN comments
ON tags.photo_id = comments.photo_id

EDITAR: Si realmente desea unir todo sin CTE, parece que da resultados correctos:

SELECT photo.photo_id,
to_json(array_agg(DISTINCT tag.*)) AS tags,
to_json(array_agg(DISTINCT comment.*)) AS comments
FROM photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id