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