Q: How to do search for JSONB column values?

[matissg]

I have positions table, where there is JSONB column data. I'm trying to search for values of ptype inside that data column.

At the moment I can do sorting for ptype, however search is not working and I get this error:
NoMethodError (undefined method "field" for "pilon":String): when I search for "pillon" in my ptype field.

My ajax-datatables-rails file looks like this:

class PositionDatatable < AjaxDatatablesRails::Base

  def view_columns
    @view_columns ||= {
      name:  { source: "Position.name", cond: :like },
      ptype: { source: "Position.data->>'ptype'", cond: find_ptype },
      id: { source: "Position.id", cond: :eq }
    }
  end

  private

  def data
    records.map do |record|
      {
        name: record.name,
        ptype: record.ptype, #can access with jsonb_accessor gem
        id: record.id
      }
    end
  end

  def get_raw_records
    Position.all
  end

  def find_ptype
    -> (ptype) { where("positions.data->>'ptype' like ?", "#{ptype}%")}
  end

end

Search for my name column is working just fine since it's regular table column.

For custom search I tried to copy idea from this example. How do I fix my search for JSONB column data, please?

[matissg]

I'd appreciate if anyone could clear out if it's possible to achieve search by data within JSONB column? In my example I have JSONB column data where I want to search by ptype values.

[n-rodriguez]

Can you please try with the master branch of the repo?

[ajahongir]

@matissg it seams master branch is okay. https://github.com/jbox-web/ajax-datatables-rails/blob/master/lib/ajax-datatables-rails/datatable/column.rb#L46
you will recieve 2 params column and formated_value

[matissg]

@ajahongir @n-rodriguez Thank you, I updated from master branch.
Now I get error ArgumentError (wrong number of arguments (given 2, expected 1)) and it points to my find_ptype, which looks like in example above.
What am I doing wrong?

[n-rodriguez]

The lambda waits for 2 params :

  def find_ptype
    -> (column, formatted_value) { where("positions.data->>'ptype' like ?", "#{ptype}%")}
  end

[ajahongir]

you will recieve 2 params column and formated_value

[ajahongir]

@n-rodriguez dont you think this feature shoud be in 0.4 branch?

[n-rodriguez]

@ajahongir : yes, I'm doing some checks before releasing a v0.4.1

[matissg]

@n-rodriguez Thank you.
I assumed I shouldn't be using "#{ptype}%" since my find_ptype doesn't know what it is, so I'm trying to do this:

def find_ptype
  -> (column, formated_value) { where("positions.data->>'ptype' like ?", "#{formated_value}%") }
end

but now I get NoMethodError (undefined method "where" for and it points to my find_ptype above.

Update

def find_ptype
  -> (column, formatted_value) { where("positions.data->>'ptype' like ?", "#{ptype}%")}
end

gives me this error: NameError (undefined local variable or method "ptype"

[ajahongir]

https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/blob/master/app/datatables/city_datatable.rb#L31

[matissg]

@ajahongir Thank you, but I can't make it to work. I even tried this one, which was in another answer for JSON column:

def find_ptype
  ->(column) { Arel.sql("JSON_EXTRACT(#{column.field}, '$.ptype')") }
end

but it's not working as well.

[ajahongir]

whats is wrong with your case?

[matissg]

@ajahongir Well, I have this error ArgumentError (wrong number of arguments (given 2, expected 1))
when I do e.g. ->(column) { column.table[column.field].eq(column.search.value.to_i + 1) }

Basically I'm trying to filter records by drop-down string value of my ptype column. I can access it with Position.data->>'ptype' and my data is JSONB column.

[ajahongir]

def find_ptype
->(column, formatted_value) {
  Arel.sql("JSON_EXTRACT(#{column.field}, '$.ptype') = #{formatted_value}")
 }
end

I think it should look like this.