Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Is there support for search on jsonb fields #1993

Closed
DavidGeismarLtd opened this issue May 26, 2021 · 2 comments
Closed

Is there support for search on jsonb fields #1993

DavidGeismarLtd opened this issue May 26, 2021 · 2 comments

Comments

@DavidGeismarLtd
Copy link

  • What would you like to be able to do? Can you provide some examples?

I have table GroupItems and table Groups. Groups has a jsonb column type called lms_data. I want to search on the field name inside lms_data. I am using the gem administrate that helps you created dashboard with search functionality. In your dashboard you can declare a field with a search configuration :


group: Field::BelongsTo.with_options(
      searchable: true,
      searchable_fields: ["lms_data->>'name'"]
),

then on search the gem will build the query dynamically, here it builds :
"SELECT \"group_items\".* FROM \"group_items\" LEFT OUTER JOIN \"groups\" ON \"groups\".\"account_id\" = 4 AND \"groups\".\"id\" = \"group_items\".\"group_id\" WHERE \"group_items\".\"account_id\" = 4 AND (LOWER(CAST(\"groups\".\"lms_data->>'name'\" AS CHAR(256))) LIKE '%adv%')"
However this throws an error :

PG::UndefinedColumn: ERROR:  column groups.lms_data->>'name' does not exist
LINE 1: ...E "group_items"."account_id" = $2 AND (LOWER(CAST("groups"."..
What is the correct syntax to query lms_data->>'name' within joined groups table ?
  • How could we go about implementing that?
group: Field::BelongsTo.with_options(
      searchable: true,
      searchable_fields: ["lms_data->>'name'"]
),
  • Can you think of other approaches to the problem?
@pablobm
Copy link
Collaborator

pablobm commented Jun 10, 2021

I see. The problem here is that we escape the column names for security reasons. This happens at:

def column_to_query(attr)
ActiveRecord::Base.connection.quote_column_name(attr)
end

Two solutions come to mind. The first one would be changing Administrate so that, somehow, we can pass field names that shouldn't be quoted at that point. Perhaps we could create a class that is a bit like Rails's own ActiveSupport::SafeBuffer, which is just a string flagged so that it is skipped by the ERB escaping. Like this:

group: Field::BelongsTo.with_options(
      searchable: true,
      searchable_fields: [Administrate::SafeString.new("lms_data->>'name'")]
),

The other option would be to implement custom search in your application, as I explain at #1218 (comment)

@pablobm
Copy link
Collaborator

pablobm commented Apr 6, 2023

Closing due to lack of activity.

@pablobm pablobm closed this as completed Apr 6, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants