🐷

2021/10/22に公開

# 比較対象の配列

``````with items as (
select 1 as id, ['dog'] as tags
union all select 2 as id, ['cat'] as tags
union all select 3 as id, ['bird'] as tags
union all select 4 as id, ['dog', 'cat'] as tags
union all select 5 as id, ['cat', 'bird'] as tags
union all select 6 as id, ['bird', 'dog'] as tags
union all select 7 as id, ['dog', 'cat', 'bird'] as tags
)
``````
``````select
*
from
items;
``````
id tags
1 [dog]
2 [cat]
3 [bird]
4 [dog,cat]
5 [cat,bird]
6 [bird,dog]
7 [dog,cat,bird]

このテーブルと `['dog', 'cat']` という配列を色々な条件で比較する場合のクエリを挙げる。

# 比較条件

## 配列内にある要素を全て持つ

id tags
4 [dog,cat]
7 [dog,cat,bird]
``````select
*
from
items
where
(
select
count(1)
from
unnest(tags) as t
where
t in ('dog', 'cat')
) = array_length(['dog', 'cat']);
``````

## 配列内の要素が同じ

id tags
4 [dog,cat]
``````select
*
from
items
where
array_length(tags) = array_length(['dog', 'cat'])
and (
select
count(1)
from
unnest(tags) as t
where
t in ('dog', 'cat')
) = array_length(['dog', 'cat']);
``````

## 配列内にある要素を1つだけ持つ

id tags
1 [dog]
2 [cat]
5 [cat,bird]
6 [bird,dog]
``````select
*
from
items
where
(
select
count(1)
from
unnest(tags) as t
where
t in ('dog', 'cat')
) = 1;
``````

## 配列内にある要素を1つ以上持つ

id tags
1 [dog]
2 [cat]
4 [dog,cat]
5 [cat,bird]
6 [bird,dog]
7 [dog,cat,bird]
``````select
*
from
items
where
(
select
logical_or(t in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
not (
select
logical_and(t not in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
exists (
select
*
from
unnest(tags) as t
where
t in ('dog', 'cat')
);
``````
``````select
*
from
items
where
(
select
count(1)
from
unnest(tags) as t
where
t in ('dog', 'cat')
) > 0;
``````

## 配列内にある要素だけで構成される

id tags
1 [dog]
2 [cat]
4 [dog,cat]
``````select
*
from
items
where
(
select
logical_and(t in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
not (
select
logical_or(t not in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
not exists (
select
*
from
unnest(tags) as t
where
t not in ('dog', 'cat')
);
``````
``````select
*
from
items
where
(
select
count(1)
from
unnest(tags) as t
where
t not in ('dog', 'cat')
) = 0;
``````

## 配列内にある要素を1つも持たない

id tags
3 [bird]
``````select
*
from
items
where
(
select
logical_and(t not in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
not (
select
logical_or(t in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
not exists (
select
*
from
unnest(tags) as t
where
t in ('dog', 'cat')
);
``````
``````select
*
from
items
where
(
select
count(1)
from
unnest(tags) as t
where
t in ('dog', 'cat')
) = 0;
``````

## 配列内にない要素を1つ以上持つ

id tags
3 [bird]
5 [cat,bird]
6 [bird,dog]
7 [dog,cat,bird]
``````select
*
from
items
where
(
select
logical_or(t not in ('dog', 'cat'))
from
unnest(tags) as t
);
``````
``````select
*
from
items
where
not (
select
logical_and(t in ('dog', 'cat'))
from
unnest(tags) as t
);
``````

ログインするとコメントできます