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

Ответ 1

Причиной этого является то, что в 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.

Ответ 2

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

Как я понимаю ваш вопрос, вы только получаете возможные дубликаты на 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. Счастливые дни! :)

Ответ 3

Если вы используете 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"}

Ответ 4

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