0

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.

John Skiles Skinner
  • 1,611
  • 1
  • 8
  • 21
  • 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., `'": "'`. – JohnH Apr 11 '23 at 22:15
  • This solves my problem. If you wrote it up as an official answer I would accept it. – John Skiles Skinner Apr 11 '23 at 22:55

1 Answers1

0

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

JohnH
  • 2,001
  • 1
  • 2
  • 13
  • The only scenario I can think of where you would actually want to do this is if the column is the value in a EAV like setup and could contain either a string or numerical. For any other type you would be better off actually using the correct operator / function. https://www.postgresql.org/docs/9.5/functions-json.html https://guides.rubyonrails.org/active_record_postgresql.html – max Apr 12 '23 at 12:23
  • @max, another use case would be storing JSON returned by calls to APIs. This might be desirable when more than one external service can provide the requested data; e.g., weather services. Some of the values from the returned JSON might be extracted and stored as discrete columns because it's of immediate use, but the raw JSON is kept in case other values are needed later. – JohnH Apr 12 '23 at 13:59
  • Yeah, but what I'm talking about is using pattern matching on the JSON cast into a string. As you have written it will match any key or value and give some pretty strange results. – max Apr 12 '23 at 14:02
  • As @JohnH suspected, I asked for this so that I could match stored JSON API results. This isn't production code, it was a one-off query to find all instances of a low-frequency API error. Pattern-matching on a string ensures I get every instance, even if the is JSON structured in an unexpected way. Sure, I could get some false positives too -- but I can easily weed those out by hand in this case. – John Skiles Skinner Apr 12 '23 at 21:10