通过文本数组中的任何值来挑战在文本数组中搜索Postgres sql

debugcn 发表于 Dev

特里尼

所以这是我的一个使查询正常工作的问题，要进行查询非常困难，我正在使用unnest，使用进行搜索@>，但没有任何效果。

我在db中有一个具有text[]字段的表，并且正在传递text[]字段以使它的任何值都应返回该行，或者，如果我不传递该值，则应返回所有它们。例如：

表：

id (int)
names (text[])

db中的数据：

id   names
---------------------------------
1    { alex }
2    { tom, john }
3    { tom, alex }
4    { rocky, simon, leon, john }

应该如何工作？

当我通过{simon}它应该只返回行4
当我通过{alex}它应该返回行1和3
当我通过{tom,leon}它应该返回行2，3并且4
当我传递null时，它应该返回行 1,2,3,4
in与row中的任何值匹配的任何参数，应返回row

CREATE OR REPLACE FUNCTION public.get_names
(
    _names TEXT[]
)
RETURNS JSONB
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN (SELECT
        json_agg(t)
    FROM
        (
            SELECT
                n.id,
                n.names
            FROM
                public.names n
            WHERE
               _names IS NULL 
               OR exists (select unnest(cast(_names as text[]))) = ANY (n.names)
        ) t);
END;
$BODY$;

我的任何查询都没有成功，但出现以下错误：

运算符不存在：boolean =可变字符
ANY / ALL（数组）不支持设置参数
子查询返回的多于一行用作表达式
和更多..

如您所见，我确实在尝试使其正常运行，但是对我而言，这非常困难，我需要您的一些帮助。

a_horse_with_no_name

该重叠操作&&会做你想要什么

select *
from test
where names && array['tom', 'leon']

上面的代码将从names右侧返回所有至少包含一个元素的行。

要处理NULL参数，可以NULL在函数中使用check

CREATE OR REPLACE FUNCTION public.get_names (_names TEXT[] default null )
  RETURNS JSONB
LANGUAGE sql
AS 
$BODY$
  SELECT jsonb_agg(t)
  FROM (
    SELECT n.id, n.names
    FROM public.test n
    WHERE _names is null 
      OR n.names && _names 
  ) t;
$BODY$;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。