Отличается столбец данных Postgresql JSON

Question

Отличается столбец данных Postgresql JSON

Пытаюсь сделать внятный режим с рельсами.

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 >

Получение ошибки PG::UndefinedFunction: ERROR: could not identify an equality operator for type json

Конвертация в Hstore для меня не вариант в этом случае. Любая работа вокруг?

48

ruby-on-rails postgresql ruby-on-rails-3 postgresql-json

Источник

user2785428 07 май '14 в 05:51

3 ответа

Решение

Извините, я опоздал на этот ответ, но он может помочь другим.

Как я понимаю ваш запрос, вы получаете только возможные дубликаты на profiles из-за того, что многие ко многим присоединяются к integrations (который вы используете, чтобы определить, какой profiles для доступа).

Из-за этого вы можете использовать новый GROUP BY особенность с 9.1:

Когда присутствует GROUP BY, недопустимо, чтобы выражения списка SELECT ссылались на разгруппированные столбцы, кроме как внутри агрегатных функций, или если разгруппированный столбец функционально зависит от сгруппированных столбцов, поскольку в противном случае было бы более одного возможного значения, которое нужно вернуть для несгруппированный столбец. Функциональная зависимость существует, если сгруппированные столбцы (или их подмножество) являются первичным ключом таблицы, содержащей несгруппированный столбец.

Так что в вашем случае вы могли бы заставить Ruby создать запрос (извините, я не знаю синтаксис Ruby, который вы используете)...

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"

Я только удалил DISTINCT от твоего SELECT пункт и добавил GROUP BY,

Ссылаясь только на id в GROUP BY, вы используете эту новую функцию, потому что все остальные profiles столбцы "функционально зависят" от первичного ключа этого идентификатора.

Каким-то удивительным образом это избавляет Postgres от необходимости проверять равенство в зависимых столбцах (т.е. json колонка в этом случае).

DISTINCT ON Решение также отлично, и явно достаточно в вашем случае, но вы не можете использовать агрегатные функции, такие как array_agg с этим. Вы можете с этим GROUP BY подход. Счастливые дни!:)

11

Источник

user2036135 07 авг '15 в 11:48

Если вы используете PG 9.4, использование JSONB вместо JSON решает эту проблему. Пример:

-- 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"}

Как вы можете видеть, PG удалось указать DISTINCT для столбца JSONB, в то время как для столбца JSON произошел сбой!

Попробуйте также следующее, чтобы увидеть, что на самом деле ключи в JSONB отсортированы:

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"}

обратите внимание, что '{"b":"2", "a": "1"}' было вставлено как '{"a": "1", "b":"2"} ", поэтому PG идентифицирует это как запись:

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

6

Источник

user5634624 03 дек '15 в 11:05

Да, к сожалению, Postgres json не реализует равенство, но jsonb делает. Так мигрирует json столбцы в jsonb и это должно работать хорошо.

2

Источник

user6232812 03 июл '19 в 12:50

Другие вопросы по тегам ruby-on-rails postgresql ruby-on-rails-3 postgresql-json

user1499698 07 май '14 в 07:57 2014-05-07 07:57 · Accepted Answer · 2014-05-07 07:57

Причина этого в том, что в PostgreSQL (до 9.3) не определен оператор равенства для json (т.е. val1::json = val2::json всегда будет выбрасывать это исключение) - в 9.4 будет один для jsonb тип.

Один из обходных путей, вы можете бросить свой json поле для text, Но это не охватит все уравнения JSON. f.ex. {"a":1,"b":2} должно быть равно {"b":2,"a":1}, но не будет равным, если приведен text,

Другой обходной путь (если у вас есть первичный ключ для этой таблицы - что должно быть), вы можете использовать DISTINCT ON (<expressions>) форма:

u.profiles.select("DISTINCT ON (profiles.id) profiles.*")

Примечание: одна известная оговорка для DISTINCT ON:

Выражения DISTINCT ON должны соответствовать крайнему левому выражению ORDER BY. Предложение ORDER BY обычно содержит дополнительные выражения, которые определяют желаемый приоритет строк в каждой группе DISTINCT ON.