Is there support for search on jsonb fields

[DavidGeismarLtd]

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

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

https://github.com/thoughtbot/administrate/blob/b3bb0a3b7833deceff71cfe1687ac36cec12a6e8/lib/administrate/search.rb#L145-L147

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 https://github.com/thoughtbot/administrate/pull/1218#issuecomment-821292428

[pablobm]

Closing due to lack of activity.