trigram matching on a jsonb array Hello I'm trying to implement some sort of fuzzy string matching using pg_trgm on my table which contains a jsonb array,
here is a sample data:

      name
------------------
[{"firstname": "jhon", "lastname": "doe"},{"firstname": "jhonny", "lastname": "doe"}]
------------------    
[{"firstname": "jane", "lastname": "doe"},{"firstname": "janne", "lastname": "doe"}]


and this is a sample of my current query:

SELECT * FROM persons WHERE exists(
    SELECT * FROM json_array_elements(persons.name::json)
        WHERE word_similarity('jhon', (COALESCE(value-&gt;&gt;'firstname', '') || ' ' || (COALESCE(value-&gt;&gt;'lastname', ''))) ) &gt;= 0.7);


It does the job but it's slow(no index).
My question is what can I do to speed up the searching (index, alternative query) ?
TIA
You can't index a rows-returning expression, so it would probably be best to normalize your data with one nickname per row, rather all stuffed into an array.  Then you could index the "nickname" table and join it to the "persons" table.

If you don't want to do that, you could transform the name field into a string, and index that.  This could lead to unwanted results if the query could span over the delimiter and still meet the cutoff (not very likely, unless the query also contains punctuation), so the queries are not identical, but might be good enough.

create or replace function arr_val_agg(jsonb) returns text as $$ 
  select string_agg(bar.value,', ') from jsonb_array_elements($1) 
    join lateral jsonb_each_text(value) bar on true;
$$ language SQL immutable;

create index on persons using gin (arr_val_agg(name) gin_trgm_ops );

select * from persons where arr_val_agg(name) %&gt; 'jhon';


As the lengths of arr_val_agg get longer, the more false positives you are likely to get from the trigram index which need to be filtered out by a recheck, so performance might suffer.