Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_data_table (   
  id    bigint generated by default as identity PRIMARY KEY,
  city text);

select setseed(.42);
insert into test_data_table(city) select (random()*1e6)::int
from generate_series(1,5e5);

CREATE TABLE test_filter_values (
    id    bigint generated by default as identity PRIMARY KEY,
    type  text,
    value jsonb );

INSERT INTO test_filter_values (type, value) VALUES 
 ('CITY', (SELECT jsonb_agg(DISTINCT city) FROM test_data_table));

CREATE TABLE

setseed

SELECT 1

INSERT 0 500000

CREATE TABLE

INSERT 0 1

prepare array_elements as 
SELECT city
FROM test_filter_values
CROSS JOIN jsonb_array_elements_text(value)_(city)
WHERE type = 'CITY'
AND city ilike '%5';

explain analyze verbose execute array_elements;
--execute array_elements;

PREPARE

QUERY PLAN

Nested Loop  (cost=0.00..25.93 rows=80 width=32) (actual time=65.795..326.608 rows=39515 loops=1)

  Output: _.city

  ->  Seq Scan on public.test_filter_values  (cost=0.00..20.12 rows=4 width=32) (actual time=0.011..0.015 rows=1 loops=1)

        Output: test_filter_values.id, test_filter_values.type, test_filter_values.value

        Filter: (test_filter_values.type = 'CITY'::text)

  ->  Function Scan on pg_catalog.jsonb_array_elements_text _  (cost=0.00..1.25 rows=20 width=32) (actual time=65.780..323.085 rows=39515 loops=1)

        Output: _.city

        Function Call: jsonb_array_elements_text(test_filter_values.value)

        Filter: (_.city ~~* '%5'::text)

        Rows Removed by Filter: 353761

Planning Time: 0.387 ms

Execution Time: 328.928 ms

EXPLAIN

prepare jsonpath as 
SELECT jsonb_path_query( value
                        ,'$[*]?(@ like_regex ".*5$" flag "i")'
                       )#>>'{}' AS city
FROM test_filter_values 
WHERE type = 'CITY';

explain analyze verbose execute jsonpath;
--execute jsonpath;

PREPARE

QUERY PLAN

Result  (cost=0.00..100.16 rows=4000 width=32) (actual time=226.968..497.175 rows=39515 loops=1)

  Output: ((jsonb_path_query(value, '$[*]?(@ like_regex ".*5$" flag "i")'::jsonpath, '{}'::jsonb, false)) #>> '{}'::text[])

  ->  ProjectSet  (cost=0.00..40.16 rows=4000 width=32) (actual time=226.962..488.692 rows=39515 loops=1)

        Output: jsonb_path_query(value, '$[*]?(@ like_regex ".*5$" flag "i")'::jsonpath, '{}'::jsonb, false)

        ->  Seq Scan on public.test_filter_values  (cost=0.00..20.12 rows=4 width=32) (actual time=0.009..0.013 rows=1 loops=1)

              Output: id, type, value

              Filter: (test_filter_values.type = 'CITY'::text)

Planning Time: 0.087 ms

Execution Time: 498.780 ms

EXPLAIN