PostgreSQL中ILIKE查询JSON数组与普通表结果不一致问题

问题背景 #

数据库内有一张存储6-8百万条静态数据的表，为优化查询性能，计划将州、城市、标签这类重复度高的字段提取到单独的过滤表，采用JSON数组格式存储。

测试环境示例 #

原始数据表示例 #

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));

核心问题 #

直接查询原始表时，SELECT DISTINCT city from test_data_table WHERE city ILIKE 'b%';能返回4条结果；但查询过滤表展开的JSON数组时，SELECT city FROM (SELECT jsonb_array_elements(value) AS city FROM test_filter_values WHERE type = 'CITY') cities WHERE city::text ILIKE 'b%';无结果返回，精确匹配SELECT state FROM (SELECT jsonb_array_elements(value) AS state FROM test_filter_values WHERE type = 'STATE') states WHERE state::text = 'NC';同样无结果。

解决方案 #

问题根源是JSON元素的类型转换错误：jsonb_array_elements返回的是jsonb类型值，直接用::text转换会给原始字符串包裹双引号（比如'Bourbonnais'会变成'"Bourbonnais"'），导致匹配逻辑失效。

正确查询方式 #

方式1：使用->>操作符提取纯字符串

-- 模糊查询城市
SELECT city FROM (
    SELECT jsonb_array_elements(value)->>'0' AS city 
    FROM test_filter_values 
    WHERE type = 'CITY'
) cities 
WHERE city ILIKE 'b%';

-- 精确匹配州
SELECT state FROM (
    SELECT jsonb_array_elements(value)->>'0' AS state 
    FROM test_filter_values 
    WHERE type = 'STATE'
) states 
WHERE state = 'NC';

方式2：使用jsonb_text函数转换类型

-- 模糊查询城市
SELECT city FROM (
    SELECT jsonb_text(jsonb_array_elements(value)) AS city 
    FROM test_filter_values 
    WHERE type = 'CITY'
) cities 
WHERE city ILIKE 'b%';

-- 精确匹配州
SELECT state FROM (
    SELECT jsonb_text(jsonb_array_elements(value)) AS state 
    FROM test_filter_values 
    WHERE type = 'STATE'
) states 
WHERE state = 'NC';

性能优化建议 #

若需频繁查询过滤表，建议改用text[]类型存储数组，比JSON数组的查询效率更高：

-- 创建优化后的过滤表
CREATE TABLE test_filter_values_optimized
(
    id    BIGSERIAL PRIMARY KEY,
    type  text,
    value text[]
);

-- 插入数据
INSERT INTO test_filter_values_optimized (type, value)
VALUES ('CITY', (SELECT array_agg(DISTINCT city) FROM test_data_table));

INSERT INTO test_filter_values_optimized (type, value)
VALUES ('STATE', (SELECT array_agg(DISTINCT state) FROM test_data_table));

-- 简化查询
SELECT unnest(value) AS city 
FROM test_filter_values_optimized 
WHERE type = 'CITY' AND unnest(value) ILIKE 'b%';

内容的提问来源于stack exchange，提问作者Юрий Яхница