[SOLVED] ActiveRecord query jsonb with LIKE as though it were a string

ActiveRecord query jsonb with LIKE as though it were a string

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.

Solution

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., '": "'.