用文本数组中的任意值来挑战postgres在文本数组中的sql搜索

s5a0g9ez 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(355)

所以我的问题是如何使一个查询工作，这是非常具有挑战性的，我使用 unnest ，搜索使用 @> ，什么都没用。
我在db有一张table text[] 田野，我路过 text[] 字段中的任何值都应该返回该行，或者如果我不传递该值，它应该返回所有值。例如：
表格：

id (int)
names (text[])

数据库中的数据：

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 匹配行中任何值的参数应返回行

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

我的任何查询都没有成功，但出现了以下错误：
运算符不存在：布尔值=字符变化
any/all（数组）不支持set参数
用作表达式的子查询返回多行
还有更多。。
正如你所看到的，我真的很想让它工作，但对我来说这是超级困难，我需要你的帮助。

sql postgresql Arrays Function plpgsql

来源：https://stackoverflow.com/questions/63295729/challenging-postgres-sql-searching-in-text-array-by-any-value-from-text-array

2条答案

按热度按时间

mkh04yzy1#

重叠运算符 && 我会做你想做的

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

上面将返回所有行，其中 names 至少包含右侧的一个元素。
要处理空参数，可以使用 NULL 检查你的功能

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

赞(0）回复(0）举报 2021-07-24

ncgqoxb02#

使用以下方法编写函数 && 操作员：

CREATE OR REPLACE FUNCTION public.get_names (_names TEXT[] default null )
RETURNS JSONB
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN 

    (SELECT
        json_agg(t)
    FROM
        (
            SELECT
                id,
                names
            FROM
                public.test n
            WHERE
                case 
                when _names IS not NULL then names && _names 
                else 1=1
                end
        ) t);
END;
$BODY$;

演示

赞(0）回复(0）举报 2021-07-24

我来回答

用文本数组中的任意值来挑战postgres在文本数组中的sql搜索

2条答案

相关问题

热门标签

最新问答