ActiveRecord query jsonb with LIKE as though it were a string

Question

I want to write a Rails ActiveRecord query that can match a jsonb database column with SQL LIKE wildcards, as though it were a string or text field. In other words, I am trying to match a substring within the jsonb, no matter what key or value that substring may appear in.

Trying the normal way to query a string with LIKE does not work:

MyModel.where("a_jsonb_column LIKE ?", "%substring%").first

This attempt returns the error:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: jsonb ~~ unknown
LINE 1: ...on_at" FROM "my_model" WHERE (a_jsonb_column LIKE '%subs...
                                                        ^

It seems to have a problem with SQL LIKE, presumably because it is unavailable for this data type. Perhaps I'll have to cast the jsonb as a string? I'm not sure of the best way to do this in ActiveRecord. My database is Postgres 14.7 via homebrew.

Answer 1

One of the functions of ActiveRecord is to insulate the application code from database specifics. Using literal SQL diminishes its effectiveness at this; however, doing so might be acceptable when database dependency is not a concern. You could cast the column to text using a_jsonb_column::text. Keep in mind that it's possible that substring could match yet not exist in any key or value; e.g., '": "'.