Distinctes sur Postgresql colonne de données JSON

Essayer de faire distinctes sur un mode avec les rails de.

2.1.1 :450 > u.profiles.select("profiles.*").distinct
Profile Load (0.9ms)  SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" WHERE "integrations"."user_id" = $1  [["user_id", 2]]
PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integ...
^
: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" WHERE "integrations"."user_id" = $1
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integ...
^
: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" WHERE "integrations"."user_id" = $1
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/rack-mini-profiler-0.9.1/lib/patches/sql_patches.rb:109:in `prepare'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/rack-mini-profiler-0.9.1/lib/patches/sql_patches.rb:109:in `prepare'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:834:in `prepare_statement'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:795:in `exec_cache'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:139:in `block in exec_query'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract_adapter.rb:442:in `block in log'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activesupport-4.0.4/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract_adapter.rb:437:in `log'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:908:in `select'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/querying.rb:36:in `find_by_sql'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:585:in `exec_queries'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/association_relation.rb:15:in `exec_queries'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:471:in `load'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:220:in `to_a'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:573:in `inspect'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/railties-4.0.4/lib/rails/commands/console.rb:90:in `start'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/railties-4.0.4/lib/rails/commands/console.rb:9:in `start'
from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/railties-4.0.4/lib/rails/commands.rb:62:in `<top (required)>'
from bin/rails:4:in `require'
from bin/rails:4:in `<main>'2.1.1 :451 >

D'avoir une erreur PG::UndefinedFunction: ERROR: could not identify an equality operator for type json

La conversion à Hstore n'est pas une option pour moi dans ce cas. Toutes les solutions?

InformationsquelleAutor Mohamed El Mahallawy | 2014-05-07

54

La raison derrière cela est que, dans PostgreSQL (jusqu'à 9.3) il n'y a pas d'égalité définie par l'opérateur pour json (c'est à dire val1::json = val2::json toujours lever cette exception) -- dans 9.4 il y en aura un pour les jsonb type.

Une solution de contournement, vous pouvez lancer votre json champ de text. Mais qui ne couvre pas tous json equalitions. f.ex. {"a":1,"b":2} doit être égale à {"b":2,"a":1}, mais qui ne sont pas égales si coulé à text.

Une autre solution de contournement est (si vous avez une clé primaire pour la table, ce qui devrait être), vous pouvez utiliser le DISTINCTES (<expressions>) formulaire:
```
u.profiles.select("DISTINCT ON (profiles.id) profiles.*")
```
Note: Un célèbre mise en garde pour DISTINCT ON:

DISTINCTES SUR l'expression(s) doit correspondre le plus à gauche de COMMANDE PAR l'expression(s). La clause ORDER BY contiennent normalement une expression supplémentaire(s) qui déterminent souhaité en priorité de lignes à l'intérieur de chaque groupe.
- J'ai effectivement fini par faire l'DISTINCTES SUR les profils.id. C'était la meilleure solution pour moi
- comment avez-vous jeté votre json sous la forme d'un texte dans une instruction select?
- Je suis sûr que vous avez trouvé cela maintenant, mais ::text devrait le faire.
- Trouve cette question à partir de la même erreur avec un union déclaration. Essayez probablement de l'union destincts par défaut. Si vous n'avez pas besoin d'un distinct d'union, de changer de union all prend soin de lui.
InformationsquelleAutor pozs
6

Désolé je suis en retard sur cette réponse, mais il peut aider les autres.

Que je comprends de votre question, vous obtenez seulement possible les doublons sur profiles en raison de la plusieurs-à-plusieurs se joindre à integrations (que vous utilisez pour déterminer qui profiles pour y accéder).

À cause de cela, vous pouvez utiliser une nouvelle GROUP BY fonction que de 9,1:

Lorsque le GROUPE est à l'heure actuelle, il n'est pas valable pour la liste de sélection des expressions pour faire référence à dissocier les colonnes sauf dans des fonctions d'agrégation ou si le dissocier de la colonne est fonctionnellement dépendant sur les colonnes groupées, puisqu'il n'y a plus qu'une seule valeur possible pour revenir pour un dissociée de la colonne. Une dépendance fonctionnelle existe que si les colonnes groupées (ou un sous-ensemble de celui-ci) sont la clé primaire de la table contenant la dissocier de la colonne.

Donc dans votre cas, vous pourriez obtenir des Rubis pour créer la requête (désolé, je ne connais pas la syntaxe Ruby que vous utilisez)...
```
SELECT profiles.* 
FROM "profiles" 
INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" 
INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" 
WHERE "integrations"."user_id" = $1
GROUP BY "profiles"."id"
```
J'ai seulement enlevé la DISTINCT de votre SELECT clause et a ajouté l' GROUP BY.

En ne se référant qu'à la id dans le GROUP BY, vous prenez avantage de cette nouvelle fonctionnalité, car tout le reste de la profiles les colonnes sont fonctionnellement dépendant de l'identifiant de la clé primaire.

En quelque sorte, à merveille ce qui évite la nécessité de Postgres pour faire de l'égalité des contrôles sur l'dépendante des colonnes (c'est à dire votre json colonne dans ce cas).

La DISTINCT ON solution est aussi grande, et clairement suffisant dans votre cas, mais vous ne pouvez pas utiliser les fonctions d'agrégation comme array_agg avec elle. Vous POUVEZ avec ce GROUP BY approche. Les jours heureux! 🙂

InformationsquelleAutor poshest

Si vous utilisez PG 9.4 , à l'aide de JSONB plutôt que JSON permet de résoudre ce problème
Exemple :

-- JSON datatype test 
create table t1 (id int, val json);
insert into t1 (id,val) values (1,'{"name":"value"}');
insert into t1 (id,val) values (1,'{"name":"value"}');
insert into t1 (id,val) values (2,'{"key":"value"}');
select * from t1 order by id;
select distinct * from t1 order by id;
-- JSONB datatype test 
create table t2 (id int, val jsonb);
insert into t2 (id,val) values (1,'{"name":"value"}');
insert into t2 (id,val) values (1,'{"name":"value"}');
insert into t2 (id,val) values (2,'{"key":"value"}');
select * from t2 order by id;
select distinct * from t2 order by id;
Result of running the above script :
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
1 | {"name":"value"}
1 | {"name":"value"}
2 | {"key":"value"}
ERROR:  could not identify an equality operator for type json
LINE 1: select distinct * from t1 order by id;
^
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
1 | {"name": "value"}
1 | {"name": "value"}
2 | {"key": "value"}
1 | {"name": "value"}
2 | {"key": "value"}

Comme vous pouvez le voir PG réussi à impliquer DISTINCTES sur une colonne JSONB
alors qu'il échoue sur un JSON colonne !

Essayez aussi le suit pour voir qu'en fait les clés dans la JSONB sont triés :

insert into t2 values (3, '{"a":"1", "b":"2"}');
insert into t2 values (3, '{"b":"2", "a":"1"}');
select * from t2;
1 | {"name": "value"}
1 | {"name": "value"}
2 | {"key": "value"}
3 | {"a": "1", "b": "2"}
3 | {"a": "1", "b": "2"}

note que " {"b":"2", "une":"1"}' a été insérée en tant que '{"a":"1", "b":"2"}'
à cet effet PG identifie que le même enregistrement :

select distinct * from t2;
3 | {"a": "1", "b": "2"}
2 | {"key": "value"}
1 | {"name": "value"}

InformationsquelleAutor emesika

Vous devez vous connecter pour publier un commentaire.