Fuzzy string match in postgres jsonb column

Question

I have a jsonb column in a postgres table called topics which is a literal array of strings. An example record might have this for a topics value:

['healthcare', 'fitness', 'gains', 'doyouevenliftbro']

I need to find this record among many which would match %lift%

All the other queries I'm finding on stackoverflow and the pg docs can match against doyouevenliftbro as a complete string, or can do a regex match if this is converted from json_to_recordset() or whatever, but then they go on to reference properties inside the JSON, which this doesn't have. Hoping it's simple syntax that's just evading me. Thanks!

Andy Carlson Andy Carlson · Accepted Answer · 2017-09-02T00:10:22

You could use json_array_elements to un-nest the json array to a set. Once it's a set, you can apply the like operator and throw it in a subquery.

select *
from your_table
where exists (
  select *
  from json_array_elements(your_table.topics::json)
  where value::text like '%lift%'
);

EDIT: Done more bluntly, what if you just cast the whole topics array as text and match against that?

select *
from your_table
where topics::text like '%lift%';

Fuzzy string match in postgres jsonb column

1 Answers