Filter ActiveAdmin with Postgresql json column on specific json keys

Question

I have a Deal model that features a json column called deal_info. It's actually an array of JSONs.

I'm using active admin.

For example :

deal1.deal_info = [ { "modal_id": "4", "text1":"lorem" }, 
          { "modal_id": "6", "video2":"yonak" },
          { "modal_id": "9", "video2":"boom" } ] 
deal2.deal_info = [ { "modal_id": "10", "text1":"lorem" }, 
          { "modal_id": "11", "video2":"yonak" },
          { "modal_id": "11", "image4":"boom" } ]

As first step now I would like to have a filter that would enable me to filter the deals based on the fact that deal_info json column includes at least one time the modal_id in one of its included json.

It would enable me in a select dropdown to choose for example modal_id = 6 and would filter the list of Deals to only show deal 1 (see example above).

One of the further challenge is that I need to be able to remove duplicates on the select dropdown in order not to have multiple times the same id: here for example i can't have select = [4,6,9,10,11,11]...each modal_id can only appear once.

I only found this but it did not work for me.

My current Active Admin Code

ActiveAdmin.register Deal do
  filter :modal_id,
  as: :select 
  collection: deal_info.all.to_a.map ???? 
end

EDIT after Andrei answer that helped move forward but not (yet) solve the problem

I added this code :

models/deal.rb

class Deal < ActiveRecord::Base
  store_accessor :deal_info, :modal_id
end

# using https://stackoverflow.com/questions/14605710/filter-activeadmin-with-hstore?lq=1
ransacker :modal_id do |parent|
   Arel::Nodes::InfixOperation.new('->', parent.table[:deal_info], 'modal_id')
 end

inside active admin Deal file

ActiveAdmin.register Deal do
     filter :modal_id,
     label: 'modal id',
     as: :select,
     collection: -> { Deal.all.pluck(:deal_info).flatten.map {|el| el['modal_id'] }.uniq }

It seems to PARTLY work but not completely. Indeed it does show me on the dropdown the right various modal_id's and they are well de-duplicated BUT when I select one of them on the dropdown, and click FILTER on the right side (where the filter sidebar is), it load the whole page again WITHOUT applying the filter.

For example, on my modal_id dropdown, i have the choice between ANY/4/5/8. If I choose modal_id 5 and click the FILTER button when the page with all the deals (that should be filtered on modal_id = 5) load, inside the sidebar modal_id select dropdown, I don't see 5 as I had chosen but ANY. The value of 5 was not "KEPT"/"REMEMBERED" and it got back to the value ANY. I don't understand why.

EDIT 2

I changed on AA the filter name to as 'eq'

ActiveAdmin.register Deal do
         filter :modal_id_eq,
         label: 'modal id',
         as: :select,
         collection: -> { Deal.all.pluck(:deal_info).flatten.map {|el| el['modal_id'] }.uniq }

But the problem was still the same. so I checked on found out that my ransack script was outdated and with Rails 4.2 I had to add build_quoted as show here https://github.com/activerecord-hackery/ransack/wiki/Using-Ransackers#3-search-for-a-key-in-an-hstore-column-in-this-example-searching-an-hstore-column-called-properties-for-records-containing-a-key-called-link_type

So now my code is

models/deal.rb

class Deal < ActiveRecord::Base
  store_accessor :deal_info, :modal_id
end

# using https://stackoverflow.com/questions/14605710/filter-activeadmin-with-hstore?lq=1
ransacker :modal_id do |parent|
   Arel::Nodes::InfixOperation.new('->', parent.table[:deal_info], Arel::Nodes.build_quoted('modal_id') )
end

But now I'm getting the following error. I don't know if it's due to Ransack, to active Admin or to the fact this Ransack script worked for hstore but not for my type of json column or maybe due to another reason.

ActiveRecord::StatementInvalid at /admin/deals

PG::UndefinedFunction: ERROR:  operator does not exist: json = unknown
LINE 1: ...OM "deals" WHERE "deals"."deal_info" -> 'modal_id' = '4'

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(*) FROM "deals" WHERE "deals"."deal_info" -> 'modal_id' = '4':
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract_adapter.rb:455:in `translate_exception_class'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract_adapter.rb:468:in `rescue in log'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
  activerecord (4.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `exec_no_cache'
  activerecord (4.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:584:in `execute_and_clear'
  activerecord (4.2.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:160:in `exec_query'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:336:in `select'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `block in select_all'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:83:in `cache_sql'
  activerecord (4.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `select_all'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:264:in `execute_simple_calculation'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:221:in `perform_calculation'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:127:in `calculate'
  activerecord (4.2.0) lib/active_record/relation/calculations.rb:42:in `count'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/helpers/collection.rb:9:in `collection_size'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:62:in `get_scope_count'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:40:in `block (3 levels) in build_scope'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `span'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:39:in `block (2 levels) in build_scope'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `a'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:37:in `block in build_scope'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:31:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:49:in `with_current_arbre_element'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:26:in `build_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:39:in `insert_tag'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:14:in `li'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:33:in `build_scope'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:26:in `block in build'
   () home/mathieup/.rvm/gems/ruby-2.0.0-p451@rails3tutorial2ndEd/bundler/gems/activeadmin-7aef260921d4/lib/active_admin/views/components/scopes.rb:25:in `build'
  arbre (1.0.2) lib/arbre/element/builder_methods.rb:30:in `block in build_tag'
  arbre (1.0.2) lib/arbre/context.rb:92:in `with_current_arbre_element'

they say you have to have at least postgres 9.3/9.4 to be able to use json (stackoverflow.com/questions/23098466/…) — Andrey Deineko
– Andrey Deineko, Commented Jun 30, 2015 at 18:53
json's -> operator returns json (as opposed to text, like hstore's operator) & json does not compare to text (and not even to another json), that's why you're getting the last error. You should use the ->> operator. — pozs
– pozs, Commented Jul 7, 2015 at 10:41
@pozs back from holidays sorry for late reply: it does work now a litttle better: I have put ->> with your advice in "Arel::Nodes::InfixOperation.new('->>', parent.table..." but now although I indeed don't get Rail error, I now get NO result given back by Active Admin. He should find some Deals which have modal_id=4 for example but just tells me (no Deals found). Can you provide a more detailed answer with the code? thanks — Mathieu
– Mathieu, Commented Jul 14, 2015 at 6:56
@pozs please provide an answer so that I can give you the bounty if answer is right .can't give points to a comment. — Mathieu
– Mathieu, Commented Jul 14, 2015 at 8:05

Andrey Deineko · Accepted Answer · 2015-06-28 06:27:48Z

1

+50

Check something along these lines:

filter :modal_id_eq, # note _eq here
  label: 'modal id',
  as: :select,
  collection: -> { Deal.all.pluck(:deal_info).flatten.map {|el| el['modal_id'] }.uniq }

edited Jun 28, 2015 at 6:27

answered Jun 27, 2015 at 20:57

Andrey Deineko

52.5k11 gold badges119 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mathieu Over a year ago

thanks! It seems to PARTLY work but not completely. indeed it does show me on the dropdown the right various modal_id's used by the Deals BUT when I select one of them on the dropdown, and click FILTER on the right side (where the filter sidebar is), it load the whole page again WITHOUT applying the filter. forex if I choose to filter on modal_id=5, evidence it does not work for me is I see ALL the deals AND that after clicking "filter", when the page loads, inside the sidebar modal_id select dropdown, it did not KEEP the value 5 but get back to ANY. I'll edit my question.

Andrey Deineko Over a year ago

I see you've added this custom ransacker, but you've missed tiny thing in filter name, I'll edit the answer :) I am not sure how exactly this works cause I've never did it with json, but let me know if that changed the behaviour

Mathieu Over a year ago

you were right but it did not change the error. BUT, I found out the ransack script I was using is outdated and on Rails 4.2, I had to change it to use sth called 'build_quoted' (see this page github.com/activerecord-hackery/ransack/wiki/…). But now getting a new error. I edited my question.

Mathieu Over a year ago

moved me forward enough even if did not find the final solution

bbozo Over a year ago

Made me cringe when I saw Deal.all.pluck, this is only scalable if Deal doesn't scale to more than a 100 or so records

|

rhymes · Accepted Answer · 2015-09-11 08:26:21Z

1

I'm using Rails 4.2 and activeadmin (latest revision as of 2015-09-11) and this worked flawlessly.

model:

  ransacker :card_code do |parent|
    Arel::Nodes::InfixOperation.new(
      '->>', parent.table[:data], Arel::Nodes.build_quoted('card_code'))
  end

active admin file

filter :card_code_eq, label: 'Card code'

Notice that I'm using '->>' instead of '->' since the latter returns a JSON object that needs to be cast (I believe that's the problem you were having). Check PostgreSQL functions and operators for the explanation.

Hopefully since June you solved your issue but I reckon the solution might be useful to somebody else in the future.

answered Sep 11, 2015 at 8:26

rhymes

4241 gold badge10 silver badges18 bronze badges

2 Comments

Mathieu Over a year ago

thanks for your answer. Actually no unfortunately I left this unsolved. I don't know how but I had already moved forward and was using --> instand of ->. The code inside active admin filter :modal_id_eq, as: :select, collection: -> { Deal.all.pluck(:deal_info).flatten.map {|el| el['modal_id'] }.uniq } is working: it displays on active admin right sidebar a dropdown with the values.

Mathieu Over a year ago

But my problem lies I think inside with Ransacker I think because, when I choose a modal _id inside the dropdown (for example modal_id= 6) and submit the search , the output is 0 results. But I know I have Deals which have inside the deal_info column modal_id = 6. Like deal17.deal_info = [ { "modal_id": "4", "text1":"lorem" }, { "modal_id": "6", "video2":"yonak" }, { "modal_id": "9", "video2":"boom" } ]

Collectives™ on Stack Overflow

Filter ActiveAdmin with Postgresql json column on specific json keys

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related