Postgres, searching with ILIKE operator from json array works different, than from regular table

Question

In our DB we have tables, that have 6-8 M records, this data is static and in order of performance optimization we decided to store some data from this tables in separate table, and for predefined filters use the values from this table. For example we want to store distinct states, cities, tags in this table.

The example of data could be represented in such test table:

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

And here the example of separate table for filters and the way to fill it with values:

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

So, the main issue happens when we try to query data from this table, here is the example:

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

This query returns 4 results; And this one:

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

Returns no results, LIKE operator without % at the begining or in the end returs zero results from test_filter_values table, but if using LIKE '%b%' with such case the behaviour is the same. The same issue happens with the exact match:

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

It returns no results either.

So how could we deal in this case?

Richard Huxton · Accepted Answer · 2024-03-12 10:27:02Z

2

That's because jsonb_array_elements returns jsonb not text. A JSON array might have other arrays, objects, arrays of objects etc inside it.

That means the text values will be quoted as JSON and you will be trying to match against "Anchorage" not Anchorage when that gets converted to text.

Maybe just store an array of text if that's what you want to use?

answered Mar 12, 2024 at 10:27

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Юрий Яхница Over a year ago

Thank's, your answe gave me a good idea, the problem is that in this table could be stored different types of data and I can't use just array of text, but after some research I found jsonb_array_elements_text() function, that works exactly as I expected.

Zegarek · Accepted Answer · 2024-03-12 18:10:12Z

0

If you're using jsonb, you get JSONPath expressions, and those offer like_regex filter: demo

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

city
Belle Chasse
Biscayne Park
Bonita Springs
Bourbonnais

The #>>'{}' is there to "open up" the resulting jsonb strings and take the text-type value out. Plain ::text cast would produce double quotes around the values.

It's more flexible than comparing to every element from jsonb_array_elements_text() and you can apply other JSONPath-related logic in one go. If you prefer that for some other reason and all you do is this type of comparison, there's no need to nest a subquery:

SELECT state
FROM test_filter_values
CROSS JOIN jsonb_array_elements_text(value)as t(state)
WHERE type = 'STATE'
AND state = 'NC';

And in that case it should perform comparably.

edited Mar 12, 2024 at 18:10

answered Mar 12, 2024 at 17:31

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

3 Comments

Юрий Яхница Over a year ago

Probably this solution is working, but using regex is a bit overkill, and make code hard to read and maintain for other developers, in my case jsonb_array_elements_text() solve the problem much easier.

Zegarek Over a year ago

@ЮрийЯхница ILIKE 'b%' you showed is also a regex. It's equivalent to like_regex "^b.*" flag "i", except the latter uses POSIX-style syntax, the former is SQL standard regex syntax. I don't insist on JSONPath - it's there explicitly just in case you work with jsonb a lot and you wish to do more than that single comparison. Otherwise, I agreed jsonb_array_elements_text() will definitely suffice and I proposed a clean, standard way to use it without nesting.

Zegarek Over a year ago

All unfamiliar syntax is overkill hard to read and maintain, as long as it's unfamiliar - if you don't need it for anything else, don't use it, don't read it, don't maintain it. If it ain't broke (ilike does the job), don't fix it :)

Collectives™ on Stack Overflow

Postgres, searching with ILIKE operator from json array works different, than from regular table

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related