Ransack: PG::UndefinedFunction: ERROR: could not identify an equality operator for type json

[jspillers]

When trying to use ransack on a table that has a postgresql json column it causes the following error:

PG::UndefinedFunction: ERROR: could not identify an equality operator for type json

Only occurs when passing distinct: true to #result

[jonatack]

Thanks @jspillers for the bug report, could you please provide the information mentioned in the Contributing Guide?

[marlonmantilla]

Hi guys getting same here,

Using gem 'ransack', github: 'activerecord-hackery/ransack', branch: 'rails-4.1' version.

For now I'm getting repeated results...

Started GET "/manage/customers.json?q[first_name_or_last_name_or_phone_lines_phone_number_cont_any]=mic" for 127.0.0.1 at 2014-10-30 10:23:22 -0500
Processing by Manage::CustomersController#index as JSON
  Parameters: {"q"=>{"first_name_or_last_name_or_phone_lines_phone_number_cont_any"=>"mic"}}
  User Load (0.9ms)  SELECT  "users".* FROM "users"  WHERE "users"."id" = 1  ORDER BY "users"."id" ASC LIMIT 1
  Customer Load (1.8ms)  SELECT DISTINCT "customers".* FROM "customers" LEFT OUTER JOIN "phone_lines" ON "phone_lines"."phoneable_id" = "customers"."id" AND "phone_lines"."phoneable_type" = 'Customer' WHERE (((("customers"."first_name" ILIKE '%mic%') OR ("customers"."last_name" ILIKE '%mic%')) OR ("phone_lines"."phone_number" ILIKE '%mic%')))
PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT "customers".* FROM "customers" LEFT OUTER JO...
                        ^
: SELECT DISTINCT "customers".* FROM "customers" LEFT OUTER JOIN "phone_lines" ON "phone_lines"."phoneable_id" = "customers"."id" AND "phone_lines"."phoneable_type" = 'Customer' WHERE (((("customers"."first_name" ILIKE '%mic%') OR ("customers"."last_name" ILIKE '%mic%')) OR ("phone_lines"."phone_number" ILIKE '%mic%')))
Completed 500 Internal Server Error in 8ms

PG::UndefinedFunction - ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT "customers".* FROM "customers" LEFT OUTER JO...
                        ^
:
  activerecord (4.1.0) lib/active_record/connection_adapters/postgresql_adapter.rb:815:in `block in exec_no_cache'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
  activesupport (4.1.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
  activerecord (4.1.0) lib/active_record/connection_adapters/postgresql_adapter.rb:815:in `exec_no_cache'
  activerecord (4.1.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
  activerecord (4.1.0) lib/active_record/connection_adapters/postgresql_adapter.rb:947:in `select'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract/database_statements.rb:31:in `select_all'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract/query_cache.rb:67:in `block in select_all'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract/query_cache.rb:82:in `cache_sql'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract/query_cache.rb:67:in `select_all'
  activerecord (4.1.0) lib/active_record/querying.rb:39:in `find_by_sql'
  activerecord (4.1.0) lib/active_record/relation.rb:603:in `exec_queries'
  activerecord (4.1.0) lib/active_record/relation.rb:487:in `load'
  activerecord (4.1.0) lib/active_record/relation.rb:231:in `to_a'
  activerecord (4.1.0) lib/active_record/relation.rb:236:in `as_json'
  activesupport (4.1.0) lib/active_support/core_ext/object/json.rb:159:in `block in as_json'
  activesupport (4.1.0) lib/active_support/core_ext/object/json.rb:159:in `as_json'
  activesupport (4.1.0) lib/active_support/json/encoding.rb:34:in `encode'
  activesupport (4.1.0) lib/active_support/json/encoding.rb:21:in `encode'
  activesupport (4.1.0) lib/active_support/core_ext/object/json.rb:37:in `to_json_with_active_support_encoder'
  actionpack (4.1.0) lib/action_controller/metal/renderers.rb:96:in `block in <module:Renderers>'
  active_model_serializers (0.9.0) lib/action_controller/serialization.rb:54:in `_render_option_json'
  actionpack (4.1.0) lib/action_controller/metal/renderers.rb:39:in `block in _handle_render_options'
  /home/marman/.rvm/rubies/ruby-2.1.2/lib/ruby/2.1.0/set.rb:263:in `each'
  actionpack (4.1.0) lib/action_controller/metal/renderers.rb:36:in `_handle_render_options'
  actionpack (4.1.0) lib/action_controller/metal/renderers.rb:32:in `render_to_body'
  actionpack (4.1.0) lib/abstract_controller/rendering.rb:25:in `render'
  actionpack (4.1.0) lib/action_controller/metal/rendering.rb:16:in `render'
  actionpack (4.1.0) lib/action_controller/metal/instrumentation.rb:41:in `block (2 levels) in render'
  activesupport (4.1.0) lib/active_support/core_ext/benchmark.rb:12:in `block in ms'
  /home/marman/.rvm/rubies/ruby-2.1.2/lib/ruby/2.1.0/benchmark.rb:294:in `realtime'
  activesupport (4.1.0) lib/active_support/core_ext/benchmark.rb:12:in `ms'
  actionpack (4.1.0) lib/action_controller/metal/instrumentation.rb:41:in `block in render'
  actionpack (4.1.0) lib/action_controller/metal/instrumentation.rb:84:in `cleanup_view_runtime'
  activerecord (4.1.0) lib/active_record/railties/controller_runtime.rb:25:in `cleanup_view_runtime'
  actionpack (4.1.0) lib/action_controller/metal/instrumentation.rb:40:in `render'
  app/controllers/manage/customers_controller.rb:8:in `block (2 levels) in index'
  actionpack (4.1.0) lib/action_controller/metal/mime_responds.rb:258:in `respond_to'
  app/controllers/manage/customers_controller.rb:6:in `index'
  actionpack (4.1.0) lib/action_controller/metal/implicit_render.rb:4:in `send_action'
  actionpack (4.1.0) lib/abstract_controller/base.rb:189:in `process_action'
  actionpack (4.1.0) lib/action_controller/metal/rendering.rb:10:in `process_action'
  actionpack (4.1.0) lib/abstract_controller/callbacks.rb:20:in `block in process_action'
  activesupport (4.1.0) lib/active_support/callbacks.rb:113:in `call'
  activesupport (4.1.0) lib/active_support/callbacks.rb:149:in `block in halting_and_conditional'
  activesupport (4.1.0) lib/active_support/callbacks.rb:166:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:166:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:229:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:229:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:166:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:166:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:166:in `block in halting'
  activesupport (4.1.0) lib/active_support/callbacks.rb:86:in `run_callbacks'
  actionpack (4.1.0) lib/abstract_controller/callbacks.rb:19:in `process_action'
  actionpack (4.1.0) lib/action_controller/metal/rescue.rb:29:in `process_action'
  actionpack (4.1.0) lib/action_controller/metal/instrumentation.rb:31:in `block in process_action'
  activesupport (4.1.0) lib/active_support/notifications.rb:159:in `block in instrument'
  activesupport (4.1.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activesupport (4.1.0) lib/active_support/notifications.rb:159:in `instrument'
  actionpack (4.1.0) lib/action_controller/metal/instrumentation.rb:30:in `process_action'
  actionpack (4.1.0) lib/action_controller/metal/params_wrapper.rb:250:in `process_action'
  activerecord (4.1.0) lib/active_record/railties/controller_runtime.rb:18:in `process_action'
  actionpack (4.1.0) lib/abstract_controller/base.rb:136:in `process'
  actionview (4.1.0) lib/action_view/rendering.rb:30:in `process'
  actionpack (4.1.0) lib/action_controller/metal.rb:195:in `dispatch'
  actionpack (4.1.0) lib/action_controller/metal/rack_delegation.rb:13:in `dispatch'
  actionpack (4.1.0) lib/action_controller/metal.rb:231:in `block in action'
  actionpack (4.1.0) lib/action_dispatch/routing/route_set.rb:80:in `dispatch'
  actionpack (4.1.0) lib/action_dispatch/routing/route_set.rb:48:in `call'
  actionpack (4.1.0) lib/action_dispatch/journey/router.rb:71:in `block in call'
  actionpack (4.1.0) lib/action_dispatch/journey/router.rb:59:in `call'
  actionpack (4.1.0) lib/action_dispatch/routing/route_set.rb:676:in `call'
  dragonfly (1.0.7) lib/dragonfly/middleware.rb:14:in `call'
  warden (1.2.3) lib/warden/manager.rb:35:in `block in call'
  warden (1.2.3) lib/warden/manager.rb:34:in `call'
  rack (1.5.2) lib/rack/etag.rb:23:in `call'
  rack (1.5.2) lib/rack/conditionalget.rb:25:in `call'
  rack (1.5.2) lib/rack/head.rb:11:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/params_parser.rb:27:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/flash.rb:254:in `call'
  rack (1.5.2) lib/rack/session/abstract/id.rb:225:in `context'
  rack (1.5.2) lib/rack/session/abstract/id.rb:220:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/cookies.rb:560:in `call'
  activerecord (4.1.0) lib/active_record/query_cache.rb:36:in `call'
  activerecord (4.1.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:621:in `call'
  activerecord (4.1.0) lib/active_record/migration.rb:380:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
  activesupport (4.1.0) lib/active_support/callbacks.rb:82:in `run_callbacks'
  actionpack (4.1.0) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/reloader.rb:73:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
  better_errors (2.0.0) lib/better_errors/middleware.rb:84:in `protected_app_call'
  better_errors (2.0.0) lib/better_errors/middleware.rb:79:in `better_errors_call'
  better_errors (2.0.0) lib/better_errors/middleware.rb:57:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
  railties (4.1.0) lib/rails/rack/logger.rb:38:in `call_app'
  railties (4.1.0) lib/rails/rack/logger.rb:20:in `block in call'
  activesupport (4.1.0) lib/active_support/tagged_logging.rb:68:in `block in tagged'
  activesupport (4.1.0) lib/active_support/tagged_logging.rb:26:in `tagged'
  activesupport (4.1.0) lib/active_support/tagged_logging.rb:68:in `tagged'
  railties (4.1.0) lib/rails/rack/logger.rb:20:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/request_id.rb:21:in `call'
  rack (1.5.2) lib/rack/methodoverride.rb:21:in `call'
  rack (1.5.2) lib/rack/runtime.rb:17:in `call'
  activesupport (4.1.0) lib/active_support/cache/strategy/local_cache_middleware.rb:26:in `call'
  dragonfly (1.0.7) lib/dragonfly/cookie_monster.rb:9:in `call'
  rack (1.5.2) lib/rack/lock.rb:17:in `call'
  actionpack (4.1.0) lib/action_dispatch/middleware/static.rb:64:in `call'
  rack (1.5.2) lib/rack/sendfile.rb:112:in `call'
  railties (4.1.0) lib/rails/engine.rb:514:in `call'
  railties (4.1.0) lib/rails/application.rb:144:in `call'
  rack (1.5.2) lib/rack/lock.rb:17:in `call'
  rack (1.5.2) lib/rack/content_length.rb:14:in `call'
  rack (1.5.2) lib/rack/handler/webrick.rb:60:in `service'
  /home/marman/.rvm/rubies/ruby-2.1.2/lib/ruby/2.1.0/webrick/httpserver.rb:138:in `service'
  /home/marman/.rvm/rubies/ruby-2.1.2/lib/ruby/2.1.0/webrick/httpserver.rb:94:in `run'
  /home/marman/.rvm/rubies/ruby-2.1.2/lib/ruby/2.1.0/webrick/server.rb:295:in `block in start_thread'

[jonatack]

Hi @marlonmantilla, we no longer actively maintain the 4.1 branch (it's up to the community to do so if they wish). It may not solve the problem, but did you try with Ransack master? Thanks.

[Emerson]

Seeing this on master as well

[Emerson]

This is probably the same issue outlined here: https://github.com/mbleigh/acts-as-taggable-on/pull/496

[ridget]

Ran into this error before, when calling uniq on a model that had a json field in postgres 9.3. If you are able to upgrade, switching the column to type jsonb and updating to >= postgres 9.4 will fix this.

[seanlinsley]

I can confirm that upgrading to Postgres 9.4 and using JSONB fixes this.

select '{}'::json = '{}'::json;
ERROR:  operator does not exist: json = json

select '{}'::jsonb = '{}'::jsonb;
 ?column? 
----------
 t
(1 row)

[seanfcarroll]

I suggest we add to the README that jsonb in Postgres 9.4+ should be used for json-type fields in Ransack operations.

[pnomolos]

@seanfcarroll You should note that you'll have to use a JSONB column in PG 9.4+ (or note that in general JSON columns will not work). The non-JSONB JSON types have no equality operator, hence the original error (DISTINCT with no arguments compares every column of each record, including those with no equality operator).

[jonatack]

For what it's worth, SQL DISTINCT is a bit of a code smell. It can mean the queries are not well written and that they are needlessly loading duplicates.

[seanfcarroll]

Closed due to inactivity