element_at(array(E), index)

arrayの要素の1つ目を取得する

SELECT ARRAY_AGG(value)
-- => ["バナナ","みかん","蒸しパン"]

SELECT element_at(ARRAY_AGG(value), 1)
-- => "バナナ"

cardinality(x)

arrayの要素数。length、count

SELECT ARRAY_AGG(value)
-- => ["バナナ","みかん","蒸しパン"]

SELECT cardinality(ARRAY_AGG(value))
-- => 3

array_join(x, delimiter, null_replacement)

GROUP_CONCATと同じことしたいよ

SELECT ARRAY_AGG(value)
-- => ["バナナ","みかん","蒸しパン"]

SELECT array_join(ARRAY_AGG(value), ';')
-- => "バナナ";"みかん";"蒸しパン"

array_sort, array_distinct

ユニークにして並び替える

SELECT ARRAY_AGG(value)
-- => ["222","444","444","222","555","444","111","333","444","333"]

-- 全部同じ
SELECT ARRAY_AGG(DISTINCT p_date order by p_date)
SELECT array_sort(ARRAY_AGG(DISTINCT p_date))
SELECT array_sort(array_distinct(ARRAY_AGG(p_date)))
-- => ["111","222","333","444","555"]

array_aggの中ではorder byもdistinctも使える

FILTER()

リファレンスにはfilter(array(T), function(T, boolean)) -> array(T)って書いてある
arrayの対象を絞りたい場合

SELECT ARRAY_AGG(key)
-- => ["AAA","ABC","BBB","CCC","AZZ","YYY","A____"]

SELECT ARRAY_AGG(key) FILTER(where REGEXP_LIKE(key, 'A'))
-- => ["AAA","ABC","AZZ","A____"]

any_match, all_match

配列の中に該当条件の要素を1件以上含んでいるかのT/Fの判定に使う。booleanが返ってくる。

SELECT ARRAY_AGG(value)
-- => ["AAA","ABC","BBB","CCC","AZZ","YYY","A____"]

SELECT any_match(value, v -> v like 'C%')
-- => ture
-- どれか1つでも存在していればtrue。1つもなければfalse

すべての要素が条件に一致しなければいけない、というall_matchもある

ちなみに、any_matchにセットする配列が空の場合はnullになるので
完全にtrue/falseどちらかの戻り値を期待する場合は、事前にnullは弾かないといけない

ここからはおまけ

max_by, min_by

arrayじゃないけど
group by したときに、そのカラムの中の最大・最小(MAX, MIN)ではなくAカラムが最大・最小のレコードのBの値が欲しい時がある
それが取れるのでめちゃ便利

-- group by でまとめられる対象のレコード
-- date, value
-- "2019-01-01","AAA"
-- "2019-01-02","ZZZ"
-- "2019-01-03","BBB"

-- 全部同じ
SELECT max_by(value, date)
-- dateがMAXのレコードのvalueの値が取れる
-- => "BBB"

map_agg

arrayじゃないけど
ハッシュにしてくれる
ハッシュなので同じキーがあると1つにまとめられるので注意

SELECT map_agg(date, value)
-- => {"2019-01-01":"AAA", "2019-01-02":"ZZZ", "2019-01-03","BBB"}

まとめのまとめ

よく使うのが出てきたら追加する