Search against jsonb

[eloy]

Hi,

Is there any official way to include jsonb fields in search_scopes?

I've tried adding them as strings, but it doesn't work.

pg_search_scope :search, against: [:foo, "data->>'name'"]

Digging into it, I've found that when pg_search builds the query, it quotes column names and generates
something like this:
(ts_rank((to_tsvector('simple', COALESCE("trials"."imported_data->>'title'"::text, ''))

while Postgres expects this:
(ts_rank((to_tsvector('simple', COALESCE("trials".imported_data->>'title'::text, ''))

[nertzy]

Currently there is no official jsonb support. It would be great to add support for that.

[bigardone]

+1

[faucct]

I have faced the same problem too, you need to allow searching against raw values, which should not be quoted. I have come up with two possible ways of checking if we should quote the field or pass it raw:

1. We check if the field to search against got any double quoutes in it, like this: "\"data\"->>'name'". I guess this one is the most simple to use and is compatible, since you are not supposed to have double quotes in column names which you are trying to quote.
2. We pass the field wrapped in something like Arel::SqlEntry.new("data->>'name'") or PgSearch.raw_field("data->>'name'").

[mattbrictson]

Would this suffice? I can open a PR.

module PgSearch
  class Configuration
    class Column
      def column_name
        # If column name is already quoted, we assume no further quoting is
        # necessary. This allows advanced SQL such as using a jsonb traversal
        # expression as the column name.
        if @column_name.include?('"')
          @column_name
        else
          @connection.quote_column_name(@column_name)
        end
      end
    end
  end
end

I tested it and it allowed me to use pg_search_scope against a jsonb field like this:

pg_search_scope :search_text, :against => %("search_json"->>'en')

[faucct]

@mattbrictson, this solution is kinda implicit, I have decided to go with the second way: now to search against the hstore column key you need to define this scope:

pg_search_scope :search_text, :against => PgSearch::Configuration::HstoreColumn.new('search_json', 'en')

The solution is super flexible: you may define your own PgSearch::Configuration::PlainColumn implementations.
You can go check it in my pull request - #272

[steve-abrams]

Has there been any jsonb implementation for pg_search_scope? I don't see anything new in the readme, and this issue is older, but still open. Would love to be able to add some scopes as mentioned in the initial post by Eloy.

[tkhuynh]

I wonder if search against jsonb is available, it would be awesome. Thanks

[karensg]

Agreed, this is definitely what this gem needs

[sungsong88]

Any update for this yet??

[svegaca]

+1

[brunohkbx]

+1

[zealot128]

I've hacked together a solution for our usecase.

Our Table has a column "keywords" with a json object of structure: [{name: "tag1", count: 2}, {name: "tag2", count: 3}] etc.. Searching for Stuff, that is in "name" does not seem work out of the box with "column::text", like the coercion of pg_search does.

First, i've "patched" pg_search in an initializer:

module PgSearchPatch
  def full_name
    if @column_name.include?('"') || @column_name.include?("'")
      @column_name
    else
      super
    end
  end
end

PgSearch::Configuration::Column.prepend PgSearchPatch

Second, now I've been able to search for arbitrary column expressions:

class SomeModel < ApplicationRecord
 pg_search_scope :search,
  against: [
   "(
        SELECT array_agg(name) 
        FROM (
          select json_extract_path_text(json_array_elements(keywords), 'name')::text as name 
          from some_models inner_table where inner_table.id = some_models.id
        ) as bb
     )"
  ]

Having the nested structure with keywords[]->name, i've need to extract that with a set operation, rejoin later as an array, that can be searched without problems.

For our "smallish" data of ~ couple of hundreds - 1k searchable records per user, there seems to be no performance implication.

[mountriv99]

+1

[KyleRAnderson]

+1

[lanks]

+1

[guilhermegazzinelli]

+1

[kaysiz]

+1

[guilhermegazzinelli]

+1

[gustavocarvalho07]

+1

[bjeanes]

I went with a slightly different variant which didn't rely on the custom column including quote characters:

module PgSearchPatch
  def initialize(column_name, weight, model)
    super

    # Re-set this field if it is a SqlLiteral (super sets it with .to_s)
    if column_name.is_a?(Arel::Nodes::SqlLiteral)
      @column_name = column_name
    end
  end

  def full_name
    if @column_name.is_a?(Arel::Nodes::SqlLiteral)
      @column_name
    else
      super
    end
  end
end

PgSearch::Configuration::Column.prepend PgSearchPatch

That allows:

  pg_search_scope :search, against: [:name, :email], associated_against: {
    profile: [:contact_email, :name, Arel.sql("metadata->'field'"), :mobile_number]
  }

I think the use of Arel::Nodes::SqlLiteral is more semantically meaningful and consistent with other parts of the ActiveRecord/Arel API when accepting raw SQL.

[kyrofa]

Has anyone actually proposed a PR for this? I think @bjeanes' approach is best for the smallest fix that solves the problem, personally.