Найти строки, в которых текстовый массив содержит значение, похожее на ввод

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

Что я думал и делал до сих пор, так это использовать оператор 'ANY' и 'LIKE' следующим образом:

select * from someTable where '%someInput%' LIKE ANY(someColum);

Но это не работает. Запрос возвращает те же значения, что и этот запрос:

select * from someTable where 'someInput' = ANY(someColum);

Я получил хороший результат, используя функцию unnest() в подзапросе, но мне нужно запросить это в предложении WHERE, если это возможно.

Почему оператор LIKE не работает с оператором ANY, и я не получаю никаких ошибок? Я думал, что одной из причин должно быть то, что оператор ANY находится в правой части запроса, но...

Есть ли какое-либо решение для этого без использования unnest() и возможно ли это в пункте WHERE?


postgresql arrays unnest sql-like any
person Jose Hermosilla Rodrigo    schedule 07.01.2016    source источник
comment
Решение без использования unnest состояло бы в том, чтобы правильно нормализовать вашу модель данных.   -  person a_horse_with_no_name    schedule 07.01.2016
comment
Я думал создать новую таблицу вместо столбца этого типа данных, если не было другого решения. Я сделаю это, так что! Спасибо за Ваш ответ!   -  person Jose Hermosilla Rodrigo    schedule 07.01.2016

Ответы (4)


arrow_upward
13
arrow_downward

Также важно понимать, что ANY — это не оператор, а конструкция SQL, которую можно использовать только справа от оператора. Более:

Оператор LIKE — или, точнее: выражение, которое внутри Postgres переписывается с помощью оператора ~~ — ожидает значение слева и шаблон справа. Для этого оператора нет COMMUTATOR (как для простой оператор равенства =), поэтому Postgres не может переворачивать операнды.

Ваша попытка:

select * from someTable where '%someInput%' LIKE ANY(someColum);

перевернул левый и правый операнд, поэтому '%someInput%' является значением, а элементы столбца массива someColum считаются шаблонами (а это не то, что вам нужно).

Он должен быть ANY(someColum) LIKE '%someInput%', за исключением того, что это невозможно с конструкцией ANY, которая разрешена только справа от оператора. Вы наталкиваетесь на блокпост здесь.

Связанный:

Вы можете нормализовать свой реляционный дизайн и сохранить элементы массива в отдельных строках в отдельной таблице. За исключением этого, unnest() — это решение, как вы уже нашли сами. Но хотя вас интересует только наличие хотя бы одного совпадающего элемента, EXISTS будет наиболее эффективным, избегая дублирования в результате — Postgres может остановить поиск, как только будет найдено первое совпадение:

SELECT *
FROM   tbl
WHERE  EXISTS (
    SELECT -- can be empty 
    FROM   unnest(someColum) elem
    WHERE  elem LIKE '%someInput%'
  );

Вы можете захотеть экранировать специальный символ в someInput. Видеть:

Будьте осторожны с отрицанием (NOT LIKE ALL (...)), когда может быть задействовано NULL:

person Erwin Brandstetter    schedule 07.01.2016
comment
Благодарю вас за информацию!! - person Jose Hermosilla Rodrigo; 07.01.2016

arrow_upward
8
arrow_downward

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

В моем случае решением была комбинация DISTINCT и unnest():

SELECT DISTINCT ON (id_) *
FROM (
  SELECT unnest(tags) tag, *
  FROM someTable
  ) x
WHERE (tag like '%someInput%');

unnest(tags) расширяет текстовый массив до списка строк, а DISTINCT ON (id_) удаляет дубликаты, полученные в результате расширения, на основе уникального столбца id_.

Обновить

Другой способ сделать это без DISTINCT в предложении WHERE:

SELECT *
FROM someTable 
WHERE (
  0 < (
    SELECT COUNT(*) 
    FROM unnest(tags) AS tag
    WHERE tag LIKE '%someInput%'
  )
);
person Rotareti    schedule 10.05.2018

arrow_upward
5
arrow_downward

Заведомо несовершенной возможностью может быть использование ARRAY_TO_STRING, затем используйте LIKE против результата. Например:

SELECT *
FROM someTable
WHERE ARRAY_TO_STRING(someColum, '||') LIKE '%someInput%';

Этот подход потенциально проблематичен, поскольку кто-то может выполнить поиск по двум элементам массива, если обнаружит соединяющуюся последовательность символов. Например, массив {'Hi','Mom'}, связанный с ||, вернет результат, если пользователь ввел i||M вместо someInput. Вместо этого, вероятно, можно было бы ожидать, что в этом случае не будет никакого результата, поскольку ни Hi, ни Mom по отдельности не содержат последовательности символов i||M.

person GoldDragonTSU    schedule 12.06.2018

arrow_upward
0
arrow_downward

Пожалуйста, проверьте это.

Этот ответ был именно тем, что я искал. Он также содержит несколько полезных советов (и примеров) на случай, если вам потребуется больше гибкости.

Это в основном объясняет операторы ANY(), @> и &&.

"Если вы хотите найти несколько значений, вы можете использовать оператор @>"

"@> означает, что содержит все значения в этом массиве. Если вы хотите найти, содержит ли текущий массив какие-либо значения в другом массиве, вы можете использовать &&"

person Javi    schedule 14.05.2020