Блог

Главная - Вопросы по программированию - Фильтровать текст массива SQL [] для соответствия значению, содержащему параметр

Фильтровать текст массива SQL [] для соответствия значению, содержащему параметр

#sql #arrays #postgresql

#sql #массивы #postgresql

Вопрос:

У меня есть таблица со столбцом TEXT[]. Я хочу вернуть все строки, которые имеют хотя бы одно из значений массива, содержащих мой параметр.

Прямо сейчас я делаю WHERE array_to_string(arr, ',') ilike '%myString%'

Но я чувствую, что это должен быть более оптимизированный способ выполнения такого поиска. Кроме того, я также хотел бы выполнить поиск значений, начинающихся или заканчивающихся моим параметром.

 CREATE TABLE IF NOT EXISTS my_table
(
    id                  BIGSERIAL,
    col_array           TEXT[],

    CONSTRAINT my_table_pkey PRIMARY KEY (id)
)
  
 insert into my_table(col_array)
VALUES ('{ABC,DEF}'),
       ('{FGH,IJK}'),
       ('{LMN}'),
       ('{OPQ}');
       
select * from my_table where ARRAY_TO_STRING(col_array, ',') ilike '%F%';

это работает, поскольку возвращает только первые 2 строки.

Вы можете найти sqlfiddle здесь:http://sqlfiddle.com /#!17/09632/7

Ответ №1:

Я бы использовал подзапрос:

 select t.*
from my_table t
where exists (select *
              from unnest(t.col_array) as x(e)
              where x.e ilike '%F%')

Возможно, вы захотите пересмотреть свое решение о денормализации вашей модели.

Цитата из руководства

Массивы не являются наборами; поиск определенных элементов массива может быть признаком неправильного проектирования базы данных. Рассмотрите возможность использования отдельной таблицы со строкой для каждого элемента, который будет элементом массива. Это упростит поиск и, вероятно, будет лучше масштабироваться для большого количества элементов.

Комментарии:

1. Спасибо за вашу помощь @a_horse_with_no_name, в итоге я сделал это с select 1 вместо select * , и это работает отлично. Я понимаю, что массивы не предназначены для выполнения такого рода фильтрации в SQL, и я приму это во внимание в будущем

2. Между select * и select 1 нет абсолютно никакой разницы в производительности, если вы этого ожидаете.

3. Я ожидал, что это действительно будет более производительным. У вас есть ссылка, объясняющая, почему?

4. Очевидным доказательством является то, что выражение даже не вычисляется. вы можете написать (select 1/0 from ..) , и он не выдаст ошибку, как это происходит, когда он не используется в качестве подзапроса для exists

5. Цитата из руководства » Поскольку результат зависит только от того, возвращаются ли какие-либо строки, а не от содержимого этих строк , выходной список подзапроса обычно неважен »