¿Cómo filtrar una tabla de ClickHouse por una columna de tipo Array?

Filtrar una tabla de ClickHouse por una columna de tipo Array es una tarea habitual, y el producto ofrece muchas funciones para trabajar con este tipo de columnas.

En este artículo, nos centraremos en cómo filtrar una tabla por una columna de tipo Array, pero el video de abajo cubre muchas otras funciones relacionadas con arrays:

Usaremos el ejemplo de una tabla con dos columnas, tags_string y tags_int, que contienen un array de cadenas y enteros, respectivamente.

• Cree una base de datos y una tabla de ejemplo.

CREATE DATABASE db1;

• Crear una tabla de ejemplo

CREATE TABLE db1.tags_table
(
    `id` UInt64,
    `tags_string` Array(String),
    `tags_int` Array(UInt64),
)
ENGINE = MergeTree
ORDER BY id;

• Inserta algunos datos de ejemplo en la tabla.

INSERT INTO db1.tags_table VALUES (1, ['tag1', 'tag2', 'tag3'], [1, 2, 3]), (2, ['tag2', 'tag3', 'tag4'], [2, 3, 4]), (3, ['tag1', 'tag3', 'tag5'], [1, 3, 5]);

Filtra la tabla con la función has(arr, elem) para devolver las filas en las que el array arr contiene el elemento elem.

Filtra la tabla para devolver las filas en las que el array tags_string contiene el elemento tag1.

SELECT * FROM db1.tags_table WHERE has(tags_string, 'tag1');

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘

Usa la función hasAll(arr, elems) para devolver las filas en las que todos los elementos del array elems están presentes en el array arr.

Filtra la tabla para devolver las filas en las que todos los elementos del array tags_string están presentes en el array ['tag1', 'tag2'].

SELECT * FROM db1.tags_table WHERE hasAll(tags_string, ['tag1', 'tag2']);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
└────┴────────────────────────┴──────────┘

Usa la función hasAny(arr, elems) para devolver las filas en las que al menos un elemento del array elems está presente en el array arr.

Filtra la tabla para devolver las filas en las que al menos un elemento del array tags_string está presente en el array ['tag1', 'tag2'].

SELECT * FROM db1.tags_table WHERE hasAny(tags_string, ['tag1', 'tag2']);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘

Podemos usar una función lambda para filtrar la tabla con la función arrayExists(lambda, arr).

Filtra la tabla para devolver las filas en las que al menos un elemento del Array tags_int es mayor que 3.

SELECT * FROM db1.tags_table WHERE arrayExists(x -> x > 3, tags_int);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘