jbox-web/ajax-datatables-rails

Q: How to do search for JSONB column values?

matissg opened this issue · 23 comments

matissg commented

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 commented

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 commented

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

ajahongir commented

@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 commented

@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 commented

The lambda waits for 2 params :

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

you will recieve 2 params column and formated_value

ajahongir commented

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

n-rodriguez commented

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

matissg commented

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

matissg commented

@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 commented

whats is wrong with your case?

matissg commented

@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 commented 
def find_ptype
->(column, formatted_value) {
  Arel.sql("JSON_EXTRACT(#{column.field}, '$.ptype') = #{formatted_value}")
 }
end

I think it should look like this.

matissg commented

@ajahongir Ok, I got this error:

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "column"
LINE 1: ... NULL AND (JSON_EXTRACT(data->>'ptype', '$.ptype') = column)
ajahongir commented

how should look like your right sql query?
.. AND (JSON_EXTRACT(data->>'ptype', '$.ptype') = pilon) - like this?

matissg commented

I could retrieve all "billboards" from "ptype" with this one:
SELECT * FROM positions WHERE data->>'ptype' = 'billboard';
I would need to pass in my desired ptype string value and get out filtered records.

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

how about this?

matissg commented

Got this error:

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  function json_extract(text, unknown) does not exist
LINE 1: ...ND "positions"."deleted_at" IS NULL AND (JSON_EXTRA...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
: SELECT COUNT(*) FROM "positions" WHERE "positions"."type" IN ('Position::Outdoor') AND "positions"."deleted_at" IS NULL AND (JSON_EXTRACT(data->>'ptype', '$.ptype') = 'billboard')):
matissg commented

@ajahongir In my model I have this scope:

scope :pilons, -> {
  where("position_positions.data->>'ptype' = :pilon", pilon: "pilon")
}

and then the query looks like this:

[3] pry(main)> Position::Outdoor.pilons.take(1)
  Position::Outdoor Load (2.9ms)  SELECT  "positions".* FROM "positions" WHERE "positions"."type" IN ('Position::Outdoor') AND "positions"."deleted_at" IS NULL AND (positions.data->>'ptype' = 'pilon') LIMIT $1  [["LIMIT", 1]]

As you see this part allows me to get my ptype values: (positions.data->>'ptype' = 'pilon')

Do you have any other ideas of how should I change this?
Arel.sql("JSON_EXTRACT(#{column.field}, '$.ptype') = '#{formatted_value}'")

matissg commented

@ajahongir @n-rodriguez
Looks like the solution in my case is like this:

->(column, formatted_value) { Arel.sql("positions.#{column.field} = '#{formatted_value}'") }

I needed to add my table name before column and it seems it wasn't about JSON_EXTRACT
I hope this helps for someone else, maybe you can add this to gem's docs, so people can do filtering by JSONB column values. It might be quite cool in some cases. Thank you for helping!

Update

If anyone needs here is how to do ILIKE search for JSONB column:

  def find_address
    ->(column, formated_value) {
      Arel.sql("positions.data->>'address'").matches("%#{formated_value}%")
    }
  end

Query looks like this: AND (positions.data->>'address' ILIKE '%Optio%')
In this example I do search in my positions table JSONB column data by address value, which in this exaple is "Optio".
Notice. Don't forget to add indexes for your JSONB column keys - otherwise queries will be slow.

  • 👍2
n-rodriguez commented

maybe you can add this to gem's docs, so people can do filtering by JSONB column values

Done! https://github.com/jbox-web/ajax-datatables-rails#tutorial

Thank you!

joshco commented

Thanks for the helpful thread. I ran into this myself and built upon this to find a cond function which may be more general. I have numerous fields in JSON.

My cond filter

def my_filter
    ->(column,formatted_value) { Arel.sql(column.field.to_s).matches("%#{formatted_value}%")}
end

With this, I can define my view columns with whatever Postgres JSON magic needed. ajax-datatables-rails will just pass that magic as a string to the cond filter which will build the right SQL. Without this, the default behavior was that the magic was quoted as a single string in SQL, which gave a non-existent column error.

My view_columns

def view_columns
    @view_columns ||= {
        id: { source: "Volunteer.id"},
        first_name: { source: "Volunteer.data->>'given_name'", cond: my_filter,orderable: true},
        last_name: { source: "Volunteer.data->>'family_name'", orderable: true, cond: my_filter},
        email: { source: "Volunteer.data->>'email_addresses'", orderable: true, cond: my_filter},
    }
end
  • 👍2