Compare two arrays in ClickHouse?

[Ilya Rusin]

Hi!

Any options to compare two arrays in ClickHouse?

I have two columns, each contains an array. I need to compare and get 1 in colC if arrays are equal and 0 if arrays are not equal.

colA    colB

{555,571,701,707,741,1470,4965}    {555,571,701,707,741,1470,4964}

Thank you!

[Denis Zhuravlev]

In the latest version of CH

select [111,222] A,  [111,222] B, [111,333] C, A=B ab, A=C ac

┌─A─────────┬─B─────────┬─C─────────┬─ab─┬─ac─┐
│ [111,222] │ [111,222] │ [111,333] │  1 │  0 │
└───────────┴───────────┴───────────┴────┴────┘

ClickHouse release 18.1.0, 2018-07-23

Поддержка произвольных типов в операторах сравнения (#2026).

before 18.1.0 you can use lambdas or something

select not has(groupArray(A=B), 0) ab, not has(groupArray(A=C),0) ac  from (select [111,222] A,  [111,222] B, [111,333] C) ARRAY JOIN A, B, C

┌─ab─┬─ac─┐
│  1 │  0 │
└────┴────┘