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.

[matissg]

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

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

[matissg]

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]

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

how about this?

[matissg]

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]

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

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

[n-rodriguez]

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]

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