Postgres renvoie [null] au lieu de [] pour array_agg de la table de jointure

Je suis la sélection de certains objets et de leurs balises dans Postgres. Le schéma est assez simple, trois tables:

objets id

taggings id | object_id | tag_id

tags id | tag

Je suis de rejoindre les tables comme ceci, à l'aide de array_agg pour agréger les balises dans un champ:

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

Toutefois, si l'objet n'a pas de balises, Postgres retourne ceci:

[ null ]

au lieu d'un tableau vide. Comment puis-je retourner un tableau vide quand il n'y a pas de balises? J'ai vérifié que je n'ai pas de nulle tag retourné.

La agrégation docs dire "Le confluent de la fonction peut être utilisée pour remplacer zéro ou un tableau vide pour null si nécessaire". J'ai essayé COALESCE(ARRAY_AGG(tags.tag)) as tags mais encore, elle retourne un tableau avec la valeur null. J'ai essayé de faire le deuxième paramètre de nombreuses choses (comme COALESCE(ARRAY_AGG(tags.tag), ARRAY()), mais ils ont tous entraîner des erreurs de syntaxe.

  • À mon humble avis l'agrégation devrait revenir tableau vide, je ne sais pas pourquoi ils ont décidé de retourner null. Il y a peut être une raison, mais quelque chose de renvoyer un tableau ne doit pas renvoyer de null.
InformationsquelleAutor Andy Ray | 2015-06-29
  1. 26

    Une autre option pourrait être array_remove(..., NULL) (introduit dans le 9.3) si tags.tag est NOT NULL (sinon, vous voudrez peut-être garder NULL valeurs dans le tableau, mais dans ce cas, vous ne pouvez pas faire la distinction entre un simple existant NULL tag et un NULL tag en raison de la 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 les tags ne sont pas trouvés, un tableau vide est retournée.

    • J'ai choisi cette réponse, peut-être injustement aux autres, car il implique beaucoup moins de requête de modification, et je ne se soucient pas de l'null balise cas ici.
    • C'est la réponse que j'ai utilisé trop, mais pour ceux qui souhaitent en savoir plus sur le "pourquoi", voir Patrick réponse ci-dessous avec la fonction d'agrégation de documentation postgresql.org/docs/9.5/static/functions-aggregate.html
    • De toute façon pour l'utiliser avec jsob_agg?
    InformationsquelleAutor Thomas Perl
  2. 14

    Depuis 9.4 on peut restreindre un appel de fonction d'agrégation de procéder uniquement les lignes qui correspondent à un certain critère: array_agg(tags.tag) filter (where tags.tag is not null)

    • Si tous les tags.tag sont nulles, cela renvoie null, pas un tableau vide. Est-il possible de retourner un tableau vide par défaut?
    • coalesce(array_agg(tags.tag) filter (where tags.tag is not null), '{}')
    InformationsquelleAutor Alexey Bashtanov
  3. 10

    Les docs disent que lorsque vous l'agrégation des lignes nulles, alors vous obtenez une valeur null, et la remarque sur l'utilisation de COALESCE pour traiter de ce cas précis.

    Cela ne s'applique pas à votre requête, car de la façon dont un LEFT JOIN se comporte - quand il trouve zéro lignes correspondantes, il retourne un ligne, rempli avec les valeurs null (et de l'ensemble de l'un null ligne est un tableau avec un élément de valeur null).

    Vous pourriez être tenté à l'aveuglette remplacer [NULL] avec [] dans la production, mais vous perdre la capacité de distiguish entre les objets sans les balises et tagged objets où tags.tag est null. La logique de l'application et/ou des contraintes d'intégrité peuvent ne pas permettre à ce deuxième cas de figure, mais c'est une raison de plus de ne pas supprimer une valeur null tag s'il parvient à se faufiler dans.

    Vous pouvez identifier un objet avec pas de tags (ou en général, de savoir quand un LEFT JOIN pas trouvé de correspondances) en vérifiant si le champ de l'autre côté de la condition de jointure est null. Donc dans votre cas, il suffit de remplacer

    array_agg(tags.tag)

    avec

    CASE
  WHEN taggings.object_id IS NULL
  THEN ARRAY[]::text[]
  ELSE array_agg(tags.tag)
END
    • Je pense que c'est une meilleure explication et la réponse, sauf que je remarque qu'il exige taggings.object_id être ajouté à un GROUP BY clause afin d'éviter une erreur de syntaxe ERROR: 42803: column "taggings.object_id" must appear in the GROUP BY clause or be used in an aggregate function -- est-ce que l'ajout de cette clause de modifier les résultats finaux à tous?
    • En supposant que la requête d'origine avait un GROUP BY objects.id (ce qui est nécessaire pour éviter ce même message d'erreur), le changer pour GROUP BY objects.id, taggings.object_id n'affectera pas le groupement (le JOIN condition assure que objects.id valeur ne peut jamais être associé à plusieurs distincts taggings.object_id valeurs).
    • Nick - Merci, je le pensais, mais n'a pas été positive.
    InformationsquelleAutor Nick Barnes
  4. 3

    J'ai trouvé que cela fera:

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

    ...en supposant que tags.tag est un type de texte.

    Pas sûr si peut-être que ce ne serait pas travailler dans les anciennes Postgres versions, mais je suis à l'aide de ver. 9.6 et il semble être au travail et moins encombrant que le CASE WHEN x IS NULL... GROUP BY... solution fournie plus tôt.

    • A été plus facile de trouver cette réponse que de comprendre, à partir de la documentation
    • Ce n'est rien pour moi dans 9.6, il renvoie toujours {NULL} si il n'y a pas de valeurs (dans ce cas, les balises).
    InformationsquelleAutor user9645
  5. 1

    La documentation dit que un tableau contenant NULL est retourné. Si vous voulez convertir un tableau vide, alors vous devez faire de la magie mineure:

    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;

    Cela suppose que les balises sont de text type (ou une de ses variantes); modifier la distribution, autant que nécessaire.

    L'astuce ici est que la première (et la seule) de l'élément dans un [NULL] tableau a une longueur de 0, donc si toutes les données sont renvoyées à partir tags vous retournez l'ensemble, sinon construire un tableau vide du type de droit.

    D'ailleurs, l'instruction dans la documentation sur l'utilisation de coalesce() est un peu minable: qu'est-ce que voulais dire, c'est que si vous ne voulez pas NULL comme un résultat, vous pouvez utiliser coalesce() de tourner que dans un 0 ou une autre sortie de votre choix. Mais vous avez besoin de l'appliquer à la les éléments du tableau au lieu de la matrice, ce qui, dans votre cas, ne serait pas une solution.

    • Yep, vous voulez vraiment l'inverse de coalesce, nullif, si la question est vraiment tel qu'il apparaît.
    • length(NULL) est NULL, pas 0. length(NULL) > 0 est également NULL, ce qui arrive à l'automne grâce à la ELSE cas. Mais le fait de length('') > 0, et je ne pense pas que c'est le comportement souhaité.
    • postgresql.org/docs/9.5/static/functions-aggregate.html Il est enterré dans la documentation, mais le texte est "Il convient de noter que, sauf pour les compter, ces fonctions renvoient une valeur null lorsque aucune ligne n'est sélectionnée. En particulier, la somme des n lignes renvoie null, pas de zéro, comme on pourrait s'y attendre, et array_agg retourne null plutôt que d'un tableau vide quand il n'y a pas de lignes d'entrée."
    InformationsquelleAutor Patrick