BigQueryでArray同士を比較する

比較対象の配列

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