Sql Postgres，使用iLike运算符从json数组中搜索的工作方式与从常规表中搜索不同

在我们的数据库中，我们有表，其中有6-8M条记录，这些数据是静态的，为了性能优化，我们决定将此表中的一些数据存储在单独的表中，并且对于预定义的筛选器，使用此表中的值.例如，我们希望在该表中存储不同的州、城市和标签.

数据的例子可以表示在这样的测试表中:

CREATE TABLE test_data_table (
                                 id    BIGSERIAL PRIMARY KEY,
                                 state  text,
                                 city text
);

INSERT INTO test_data_table (state, city)
    values  ('MD', 'Union Bridge'),
            ('IL', 'Bourbonnais'),
            ('NC', 'Rdelk'),
            ('FL', 'Bonita Springs'),
            ('FL', 'Unit Ave Maria'),
            ('AZ', 'N Placita Chula Vista Tucson'),
            ('TX', 'Sienna'),
            ('LA', 'Lornager'),
            ('CA', 'Empire'),
            ('MA', 'Middleton'),
            ('CO', 'Yampa'),
            ('GA', 'Spr Project Name Unit'),
            ('IN', 'Greencastle'),
            ('NV', 'Flora'),
            ('RI', 'Coventry'),
            ('WA', 'Rice'),
            ('FL', 'Orange City'),
            ('TX', 'Haltom City'),
            ('CA', 'Moraga'),
            ('LA', 'Belle Chasse'),
            ('AZ', 'E Rincon Creek Ranch Rd Tucson'),
            ('CA', 'Acampo'),
            ('AZ', 'S Avra Rd Tucson'),
            ('GA', 'Folkston'),
            ('FL', 'Biscayne Park'),
            ('FL', 'All Units Oxford'),
            ('IL', 'Volo'),
            ('MN', 'Peterson'),
            ('LA', 'Rayville'),
            ('KY', 'South Park View'),
            ('AZ', 'E Camino Emmanuel Sahuarita'),
            ('CA', 'Wrighwood'),
            ('CA', 'Los Angeles'),
            ('AZ', 'N Teal Blue Tr Tucson'),
            ('NY', 'Clifton Park'),
            ('IN', 'Frankl'),
            ('KY', 'Anchorage'),
            ('LA', 'Crown Point'),
            ('CA', 'Los BanosStruct CalcsSpan T'),
            ('IL', 'Chebanse');

下面是筛选器的分隔表的示例以及使用值填充该表的方法:

    CREATE TABLE test_filter_values
(
    id    BIGSERIAL PRIMARY KEY,
    type  text,
    value jsonb
);

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

INSERT INTO test_filter_values (type, value)
VALUES ('STATE', (SELECT json_agg(DISTINCT state)::jsonb FROM test_data_table));

因此，当我们try 从该表中查询数据时，会发生the main issue，以下是示例:

SELECT DISTINCT city from test_data_table
WHERE city ILIKE 'b%';

该查询返回4个结果； 还有这一张:

SELECT city FROM (SELECT jsonb_array_elements(value) AS city
                  FROM test_filter_values WHERE type = 'CITY') cities
WHERE city::text ILIKE 'b%';

不返回任何结果， LIKE运算符在开始或结束时不带%，从TEST_FILTER_VALUES表中返回零结果，但如果在这种情况下使用LIKE '%b%'，行为是相同的.完全匹配时也会出现同样的问题:

SELECT state FROM (SELECT jsonb_array_elements(value) AS state
                   FROM test_filter_values WHERE type = 'STATE') states
WHERE state::text = 'NC';

它也不返回任何结果.

那么在这种情况下我们该如何处理呢？