Ne pourrais pas identifier l'opérateur d'égalité de type json[] lors de l'utilisation de l'UNION

Je suis en train d'effectuer plusieurs requêtes sur une seule table à l'aide d'un UNION règle

J'ai deux tables:

  • projet (id, nom, épinglé BOOLEAN)
  • compétences (m2m à projets)

Je suis à la recherche de la première à obtenir un tableau de lignes qui ont pinned ensemble de true et remplir le reste avec les dernières inscriptions (pinned ensemble de false)

SELECT
  project.id AS project_id,
  project.name AS project_name,
  array_agg(json_build_object('skill_id', project_skills.id,'name', project_skills.skill)) AS skills
from project
LEFT OUTER JOIN project_skills on project.name = project_skills.project
WHERE project.pinned = true
GROUP BY project_id,project_name

UNION

SELECT
  project.id AS project_id,
  project.name AS project_name,
  array_agg(json_build_object('skill_id', project_skills.id,'name', project_skills.skill)) AS skills
from project
LEFT OUTER JOIN project_skills on project.name = project_skills.project
WHERE project.id != 1 AND project.pinned = false
GROUP BY project_id,project_name
ORDER BY project.create_date DESC LIMIT 5

Lors de l'exécution de cette requête , j'obtiens l'erreur ci-dessous

ERROR:  could not identify an equality operator for type json[]
LINE 7:   array_agg(json_build_object('skill_id', project_skills.id,...

Je ne comprends pas cette erreur. Est-elle défaillante parce que son train de comparer les json des colonnes de deux résultats?

Je suis en utilisant Postgresql 9.4.

OriginalL'auteur Kannaj | 2017-05-01

  1. 37

    Lorsque vous utilisez un UNION, le SGBD supprime les lignes en double, et pour ce faire il a besoin pour déterminer si deux lignes sont égales /identique. Cela signifie en regardant chaque colonne de la deux lignes, c'est de comparer et de décider si elles sont équivalentes.

    Le message d'erreur que vous voyez est l'endroit où l'une de vos colonnes est construit à l'aide de array_agg(json_build_object(...)) qui produit une valeur de type json[], qui signifie "tableau de json valeurs". Parce que Postgres ne sais pas comment faire pour comparer deux tableaux de JSON valeurs, il ne peut pas décider si votre UNION produit des doublons.

    Si vous n'avez pas de soins sur la suppression des doublons, la solution la plus simple est d'utiliser UNION ALL qui saute cette étape.

    Comme l'a souligné dans les commentaires, si vous ne souhaitez supprimer les doublons, vous pouvez convertir les valeurs de quelque chose qui a une comparaison définie par l'opérateur. La plus générale, la solution est de jeter de texte (par exemple, some_value::text ou CAST(some_value as text)) mais pour le JSON spécifiquement, vous voudrez probablement le jsonb type, qui ignore mise en forme lorsque l'on compare.

    Vous pourriez lancer json à jsonb, ou json[] à jsonb[], ou dans cet exemple, vous pouvez construire jsonb directement avec array_agg(jsonb_build_object(...)) plutôt que array_agg(json_build_object(...)).

    Si UNION est nécessaire une autre solution est de jeter à jsonb (ou à convertir le tableau de jsonb[] en conséquence), pour lesquels l'opérateur d'égalité est définie. Voir: stackoverflow.com/a/24296054/939860 ou stackoverflow.com/a/30520760/939860. (Vous perdez format d'origine, que ce qui est généralement hors de propos.)
    Avait le même problème et je viens de convertir la valeur en texte à l'aide de ::text la fois dans le groupe et de le sélectionner pour faciliter la comparaison, il s'est plaint. A été la construction d'une hiérarchie de vues, donc à un niveau plus haut, j'ai juste jeté en arrière en json si nécessaire avec ::json[]. À L'Aide De Pg 9.6.

    OriginalL'auteur IMSoP

  2. 2

    S'avère tout ce que j'avais à faire était d'utiliser UNION ALL - je suppose que ce n'ignore en essayant de comparer les json types entre les requêtes.

    Cela devrait être un commentaire à IMSoP de réponse.
    j'ai effectivement trouvé la solution avant que je puisse obtenir la réponse à ce post. j'ai juste ajouté ma réponse au lieu de supprimer le post. J'ai accepté sa réponse, de toute façon, bien que depuis il a été plus utile que le mien

    OriginalL'auteur Kannaj