Repository: squashql/squashql
Pull request #294 (2024-03-22 13:09:34)

Support array_contains in where condition


Patch:
diff --git a/bigquery/src/main/java/io/squashql/BigQueryUtil.java b/bigquery/src/main/java/io/squashql/BigQueryUtil.java
index 8b4ee5c..67086be 100644
--- a/bigquery/src/main/java/io/squashql/BigQueryUtil.java
+++ b/bigquery/src/main/java/io/squashql/BigQueryUtil.java
@@ -5,6 +5,7 @@ import com.google.cloud.bigquery.Field;
 import com.google.cloud.bigquery.LegacySQLTypeName;
 import com.google.cloud.bigquery.StandardSQLTypeName;
 import io.squashql.jdbc.JdbcUtil;
+import io.squashql.list.Lists;
 
 import java.io.ByteArrayInputStream;
 import java.io.FileInputStream;
@@ -59,6 +60,10 @@ public final class BigQueryUtil {
       type = StandardSQLTypeName.DATE;
     } else if (clazz.equals(LocalDateTime.class)) {
       type = StandardSQLTypeName.DATETIME;
+    } else if (clazz.equals(Lists.LongList.class)) {
+      type = StandardSQLTypeName.ARRAY;
+    } else if (clazz.equals(Lists.StringList.class)) {
+      type = StandardSQLTypeName.ARRAY;
     } else {
       throw new IllegalArgumentException("Unsupported field type " + clazz);
     }
diff --git a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
index 76b1a41..8a7c78c 100644
--- a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
+++ b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
@@ -78,4 +78,9 @@ public class BigQueryQueryRewriter implements QueryRewriter {
       default -> QueryRewriter.super.binaryOperation(operator, leftOperand, rightOperand);
     };
   }
+
+  @Override
+  public String arrayContains(TypedField field, Object value) {
+    return value + " in unnest(" + field.sqlExpression(this) + ")";
+  }
 }
diff --git a/bigquery/src/main/java/io/squashql/transaction/BigQueryDataLoader.java b/bigquery/src/main/java/io/squashql/transaction/BigQueryDataLoader.java
index 738b573..7720299 100644
--- a/bigquery/src/main/java/io/squashql/transaction/BigQueryDataLoader.java
+++ b/bigquery/src/main/java/io/squashql/transaction/BigQueryDataLoader.java
@@ -3,6 +3,7 @@ package io.squashql.transaction;
 import com.google.cloud.bigquery.*;
 import io.squashql.BigQueryServiceAccountDatastore;
 import io.squashql.BigQueryUtil;
+import io.squashql.list.Lists;
 import io.squashql.type.TableTypedField;
 import lombok.extern.slf4j.Slf4j;
 import org.eclipse.collections.impl.list.immutable.ImmutableListFactoryImpl;
@@ -41,7 +42,15 @@ public class BigQueryDataLoader implements DataLoader {
     TableId tableId = TableId.of(this.datasetName, tableName);
 
     List<com.google.cloud.bigquery.Field> fieldList = list.stream()
-            .map(f -> com.google.cloud.bigquery.Field.of(f.name(), BigQueryUtil.classToBigQueryType(f.type())))
+            .map(f -> {
+              if (f.type().equals(Lists.LongList.class)) {
+                return Field.of(f.name(), StandardSQLTypeName.INT64).toBuilder().setMode(Field.Mode.REPEATED).build();
+              } else if (f.type().equals(Lists.StringList.class)) {
+                return Field.of(f.name(), StandardSQLTypeName.STRING).toBuilder().setMode(Field.Mode.REPEATED).build();
+              } else {
+                return Field.of(f.name(), BigQueryUtil.classToBigQueryType(f.type()));
+              }
+            })
             .toList();
     // Table schema definition
     Schema schema = Schema.of(fieldList);
diff --git a/clickhouse/src/main/java/io/squashql/ClickHouseUtil.java b/clickhouse/src/main/java/io/squashql/ClickHouseUtil.java
index df9cc94..16a4ee4 100644
--- a/clickhouse/src/main/java/io/squashql/ClickHouseUtil.java
+++ b/clickhouse/src/main/java/io/squashql/ClickHouseUtil.java
@@ -4,6 +4,7 @@ package io.squashql;
 import com.clickhouse.data.ClickHouseColumn;
 import com.clickhouse.data.ClickHouseDataType;
 import io.squashql.jdbc.JdbcUtil;
+import io.squashql.list.Lists;
 
 import java.time.LocalDate;
 
@@ -28,6 +29,10 @@ public final class ClickHouseUtil {
       type = ClickHouseDataType.Bool.name();
     } else if (clazz.equals(LocalDate.class)) {
       type = ClickHouseDataType.Date.name();
+    } else if (clazz.equals(Lists.LongList.class)) {
+      type = "Array(Int64)";
+    } else if (clazz.equals(Lists.StringList.class)) {
+      type = "Array(String)";
     } else {
       throw new IllegalArgumentException("Unsupported field type " + clazz);
     }
diff --git a/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java b/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java
index 3eed3ae..391be1b 100644
--- a/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java
+++ b/clickhouse/src/main/java/io/squashql/query/database/ClickHouseQueryEngine.java
@@ -15,6 +15,7 @@ import io.squashql.query.Header;
 import io.squashql.table.ColumnarTable;
 import io.squashql.table.RowTable;
 import io.squashql.table.Table;
+import io.squashql.type.TypedField;
 import org.eclipse.collections.api.tuple.Pair;
 
 import java.util.HashSet;
@@ -146,5 +147,10 @@ public class ClickHouseQueryEngine extends AQueryEngine<ClickHouseDatastore> {
       // Tested with version https://github.com/ClickHouse/ClickHouse/tree/v22.10.2.11-stable
       return false;
     }
+
+    @Override
+    public String arrayContains(TypedField field, Object value) {
+      return "has(" + field.sqlExpression(this) + ", " + value + ")";
+    }
   }
 }
diff --git a/clickhouse/src/main/java/io/squashql/transaction/ClickHouseDataLoader.java b/clickhouse/src/main/java/io/squashql/transaction/ClickHouseDataLoader.java
index 2a259a5..883334b 100644
--- a/clickhouse/src/main/java/io/squashql/transaction/ClickHouseDataLoader.java
+++ b/clickhouse/src/main/java/io/squashql/transaction/ClickHouseDataLoader.java
@@ -12,6 +12,7 @@ import java.sql.PreparedStatement;
 import java.sql.SQLException;
 import java.util.Collection;
 import java.util.List;
+import java.util.StringJoiner;
 import java.util.stream.IntStream;
 
 import static io.squashql.ClickHouseUtil.classToClickHouseType;
@@ -28,10 +29,6 @@ public class ClickHouseDataLoader implements DataLoader {
     dropAndCreateInMemoryTable(this.clickHouseDataSource, table, fields, true);
   }
 
-  public void dropAndCreateInMemoryTableWithoutScenarioColumn(String table, List<TableTypedField> fields) {
-    dropAndCreateInMemoryTable(this.clickHouseDataSource, table, fields, false);
-  }
-
   public static void dropAndCreateInMemoryTable(ClickHouseDataSource clickHouseDataSource,
                                                 String table,
                                                 List<TableTypedField> fields,
@@ -44,21 +41,14 @@ public class ClickHouseDataLoader implements DataLoader {
     try (ClickHouseConnection conn = clickHouseDataSource.getConnection();
          ClickHouseStatement stmt = conn.createStatement()) {
       stmt.execute("drop table if exists " + table);
-      StringBuilder sb = new StringBuilder();
-      sb.append("(");
       int size = list.size();
+      StringJoiner joiner = new StringJoiner(",", "(", ")");
       for (int i = 0; i < size; i++) {
         TableTypedField field = list.get(i);
-        sb.append(SqlUtils.backtickEscape(field.name()))
-                .append(" Nullable(")
-                .append(classToClickHouseType(field.type()))
-                .append(')');
-        if (i < size - 1) {
-          sb.append(", ");
-        }
+        String format = !List.class.isAssignableFrom(field.type()) ? "Nullable(%s)" : "%s";
+        joiner.add(SqlUtils.backtickEscape(field.name()) + " " + String.format(format, classToClickHouseType(field.type())));
       }
-      sb.append(")");
-      stmt.execute("create table " + table + sb + "engine=Memory");
+      stmt.execute("create table " + table + joiner + "engine=Memory");
     } catch (SQLException e) {
       throw new RuntimeException(e);
     }
diff --git a/core/src/main/java/io/squashql/jdbc/JdbcUtil.java b/core/src/main/java/io/squashql/jdbc/JdbcUtil.java
index a8247f8..32b8b60 100644
--- a/core/src/main/java/io/squashql/jdbc/JdbcUtil.java
+++ b/core/src/main/java/io/squashql/jdbc/JdbcUtil.java
@@ -8,12 +8,12 @@ import io.squashql.table.Table;
 import io.squashql.type.TableTypedField;
 
 import java.math.BigInteger;
-import java.sql.*;
 import java.sql.Date;
+import java.sql.*;
 import java.time.LocalDate;
 import java.time.LocalDateTime;
 import java.util.*;
-import java.util.function.IntFunction;
+import java.util.function.BiFunction;
 import java.util.stream.Collectors;
 import java.util.stream.IntStream;
 import java.util.stream.Stream;
@@ -57,6 +57,12 @@ public final class JdbcUtil {
     if (clazz.equals(java.sql.Timestamp.class)) {
       return "TIMESTAMP";
     }
+    if (clazz.equals(Lists.LongList.class)) {
+      return "INT[]"; // FIXME duckdb only
+    }
+    if (clazz.equals(Lists.StringList.class)) {
+      return "VARCHAR[]"; // FIXME duckdb only
+    }
     throw new IllegalArgumentException("Unsupported field type " + clazz);
   }
 
@@ -96,7 +102,7 @@ public final class JdbcUtil {
    * +-----------+-------------+------------------------------+-------------+-----------+-----------+-------------+---------------+----------------+----------------+----------+---------+------------+---------------+------------------+-------------------+------------------+-------------+---------------+--------------+-------------+------------------+------------------+--------------------+
    * </pre>
    */
-  public static Map<String, Store> getStores(String catalog, String schema, Connection connection, IntFunction<Class<?>> typeToClass) {
+  public static Map<String, Store> getStores(String catalog, String schema, Connection connection, BiFunction<Integer, String, Class<?>> typeToClass) {
     try (connection) {
       DatabaseMetaData metadata = connection.getMetaData();
       Map<String, Store> stores = new HashMap<>();
@@ -105,9 +111,10 @@ public final class JdbcUtil {
         String tableName = resultSet.getString("TABLE_NAME");
         String columnName = resultSet.getString("COLUMN_NAME");
         int dataType = resultSet.getInt("DATA_TYPE");
+        String columnTypeName = resultSet.getString("TYPE_NAME");
         stores.computeIfAbsent(tableName, k -> new Store(k, new ArrayList<>()))
                 .fields()
-                .add(new TableTypedField(tableName, columnName, typeToClass.apply(dataType)));
+                .add(new TableTypedField(tableName, columnName, typeToClass.apply(dataType, columnTypeName)));
       }
       return stores;
     } catch (SQLException e) {
@@ -186,7 +193,7 @@ public final class JdbcUtil {
   public static List<?> streamToList(Class<?> listClass, Stream<Object> stream) {
     if (listClass == Lists.LongList.class) {
       return stream
-              .map(e -> e instanceof BigInteger ? ((BigInteger) e).longValueExact() : (Long) e)
+              .map(e -> e instanceof BigInteger ? ((BigInteger) e).longValueExact() : ((Number) e).longValue())
               .collect(Collectors.toCollection(Lists.LongList::new));
     } else if (listClass == Lists.DoubleList.class) {
       return stream
@@ -209,6 +216,8 @@ public final class JdbcUtil {
       return Lists.LongList.class;
     } else if (elementClass.equals(LocalDate.class)) {
       return Lists.LocalDateList.class;
+    } else if (elementClass.equals(String.class)) {
+      return Lists.StringList.class;
     } else {
       return List.class; // we convert Array to List
     }
diff --git a/core/src/main/java/io/squashql/list/Lists.java b/core/src/main/java/io/squashql/list/Lists.java
index e6c116a..1eace3f 100644
--- a/core/src/main/java/io/squashql/list/Lists.java
+++ b/core/src/main/java/io/squashql/list/Lists.java
@@ -28,6 +28,16 @@ public final class Lists {
     }
   }
 
+  public static class StringList extends ArrayList<String> {
+
+    public StringList() {
+    }
+
+    public StringList(int initialCapacity) {
+      super(initialCapacity);
+    }
+  }
+
   public static class LocalDateList extends ArrayList<LocalDate> {
 
     public LocalDateList() {
diff --git a/core/src/main/java/io/squashql/query/Functions.java b/core/src/main/java/io/squashql/query/Functions.java
index d29144c..6fb2723 100644
--- a/core/src/main/java/io/squashql/query/Functions.java
+++ b/core/src/main/java/io/squashql/query/Functions.java
@@ -86,6 +86,10 @@ public class Functions {
     return new SingleValueConditionDto(ConditionType.NEQ, value);
   }
 
+  public static ConditionDto contains(Object value) {
+    return new SingleValueConditionDto(ConditionType.ARRAY_CONTAINS, value);
+  }
+
   public static ConditionDto in(Object... values) {
     return new InConditionDto(values);
   }
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
index 117ff98..61b4ce5 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
@@ -57,6 +57,8 @@ public record CompiledCriteria(ConditionDto condition, ConditionType conditionTy
                         .collect(Collectors.joining(", ")) + ")";
         case EQ, NEQ, LT, LE, GT, GE, LIKE ->
                 expression + " " + dto.type().sqlInfix + " " + sqlMapper.apply(((SingleValueConditionDto) dto).value);
+        case ARRAY_CONTAINS ->
+                queryRewriter.arrayContains(field, sqlMapper.apply(((SingleValueConditionDto) dto).value));
         default -> throw new IllegalStateException("Unexpected value: " + dto.type());
       };
     } else if (dto instanceof LogicalConditionDto logical) {
diff --git a/core/src/main/java/io/squashql/query/database/QueryRewriter.java b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
index 880d343..a569ec1 100644
--- a/core/src/main/java/io/squashql/query/database/QueryRewriter.java
+++ b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
@@ -143,4 +143,8 @@ public interface QueryRewriter {
       default -> throw new IllegalArgumentException(operator.toString());
     }
   }
+
+  default String arrayContains(TypedField field, Object value) {
+    return "array_contains(" + field.sqlExpression(this) + ", " + value + ")";
+  }
 }
diff --git a/core/src/main/java/io/squashql/query/database/SQLTranslator.java b/core/src/main/java/io/squashql/query/database/SQLTranslator.java
index b0b3cef..dfceb7c 100644
--- a/core/src/main/java/io/squashql/query/database/SQLTranslator.java
+++ b/core/src/main/java/io/squashql/query/database/SQLTranslator.java
@@ -1,6 +1,7 @@
 package io.squashql.query.database;
 
 import com.google.common.collect.Ordering;
+import io.squashql.list.Lists;
 import io.squashql.query.compiled.CompiledCriteria;
 import io.squashql.query.compiled.CompiledOrderBy;
 import io.squashql.query.compiled.CteRecordTable;
@@ -164,10 +165,11 @@ public class SQLTranslator {
             || field.type().equals(float.class)
             || field.type().equals(boolean.class)
             || field.type().equals(Boolean.class)
-            || field.type().equals(UnknownType.class)) {
+            || field.type().equals(UnknownType.class)
+            || field.type().equals(Lists.LongList.class)) {
       // no quote
       return String::valueOf;
-    } else if (field.type().equals(String.class)) {
+    } else if (field.type().equals(String.class) || field.type().equals(Lists.StringList.class)) {
       // quote
       return s -> "'" + queryRewriter.escapeSingleQuote(String.valueOf(s)) + "'";
     } else {
diff --git a/core/src/main/java/io/squashql/query/dto/ConditionType.java b/core/src/main/java/io/squashql/query/dto/ConditionType.java
index f48ad8f..62d9f05 100644
--- a/core/src/main/java/io/squashql/query/dto/ConditionType.java
+++ b/core/src/main/java/io/squashql/query/dto/ConditionType.java
@@ -42,6 +42,10 @@ public enum ConditionType {
    * Like.
    */
   LIKE("like"),
+  /**
+   * Returns true if the array contains the element.
+   */
+  ARRAY_CONTAINS("array_contains"),
   /**
    * Static condition, true only.
    */
diff --git a/core/src/main/java/io/squashql/query/dto/SingleValueConditionDto.java b/core/src/main/java/io/squashql/query/dto/SingleValueConditionDto.java
index 918909b..0170c57 100644
--- a/core/src/main/java/io/squashql/query/dto/SingleValueConditionDto.java
+++ b/core/src/main/java/io/squashql/query/dto/SingleValueConditionDto.java
@@ -13,7 +13,7 @@ import static io.squashql.query.dto.ConditionType.*;
 @NoArgsConstructor // For Jackson
 public final class SingleValueConditionDto implements ConditionDto {
 
-  private static final EnumSet<ConditionType> supportedTypes = EnumSet.of(LT, LE, GT, GE, EQ, NEQ, LIKE);
+  private static final EnumSet<ConditionType> supportedTypes = EnumSet.of(LT, LE, GT, GE, EQ, NEQ, LIKE, ARRAY_CONTAINS);
 
   public ConditionType type;
 
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index fe0d19d..1a91e27 100644
--- a/documentation/QUERY.md
+++ b/documentation/QUERY.md
@@ -128,7 +128,7 @@ Queries can be filtered by using Criteria class.
 
 ### WHERE - filtering records
 
-A Criteria instance can contain a condition on a single field and can be build as so:
+A Criteria instance can contain a condition on a single field and can be built as so:
 ```typescript
 import { criterion, eq } from "@squashql/squashql-js"
 import {myTable} from "./table"
@@ -136,7 +136,7 @@ import {myTable} from "./table"
 const criteria = criterion(myTable.col2, eq("c"))
 ```
 
-Several criteria can be chained with AND or OR by using the methods `any` and `all`
+Several criteria can be chained with `AND` or `OR` by using the methods `any` and `all`
 
 ```typescript
 import {
@@ -162,6 +162,20 @@ GROUP BY col1, col2
 
 Condition operators available: `eq, neq, lt, le, gt, ge, _in, isNull, isNotNull, like, and, or`.
 
+If the field is an array, the condition `contains` can be used:
+
+```
+...
+.where(criterion(myTable.myArray, contains(2)))
+...
+```
+
+```sql
+...
+WHERE ARRAY_CONTAINS(myArray, 2)
+...
+```
+
 ### HAVING - filtering aggregates
 
 > **Warning**
diff --git a/duckdb/src/main/java/io/squashql/DuckDBDatastore.java b/duckdb/src/main/java/io/squashql/DuckDBDatastore.java
index 41e1291..15d5616 100644
--- a/duckdb/src/main/java/io/squashql/DuckDBDatastore.java
+++ b/duckdb/src/main/java/io/squashql/DuckDBDatastore.java
@@ -39,7 +39,7 @@ public class DuckDBDatastore implements JdbcDatastore {
     try {
       String schema = this.connection.getSchema();
       String catalog = this.connection.getCatalog();
-      return JdbcUtil.getStores(catalog, schema, getConnection(), JdbcUtil::sqlTypeToClass);
+      return JdbcUtil.getStores(catalog, schema, getConnection(), DuckDBUtil::sqlTypeToJavaClass);
     } catch (Exception e) {
       throw new RuntimeException(e);
     }
diff --git a/duckdb/src/main/java/io/squashql/DuckDBUtil.java b/duckdb/src/main/java/io/squashql/DuckDBUtil.java
index 9142b12..80739b1 100644
--- a/duckdb/src/main/java/io/squashql/DuckDBUtil.java
+++ b/duckdb/src/main/java/io/squashql/DuckDBUtil.java
@@ -20,30 +20,34 @@ public final class DuckDBUtil {
    * @param column   the first column is 1, the second is 2, ...
    * @return the equivalent java class.
    */
-  public static Class<?> sqlTypeToClass(ResultSetMetaData metaData, int column) {
+  public static Class<?> getColumnJavaClass(ResultSetMetaData metaData, int column) {
     try {
-      Class<?> klass = JdbcUtil.sqlTypeToClass(metaData.getColumnType(column));
-      // Special case for DuckDBColumnType.HUGEINT (128 bits integer). See also #getTypeValue
-      if (klass == Object.class) {
-        String columnTypeName = metaData.getColumnTypeName(column);
-        if (columnTypeName.equals(DuckDBColumnType.HUGEINT.name())) {
-          klass = long.class;
-        }
-      } else if (klass == List.class) {
-        String typeName = metaData.getColumnTypeName(column);
-        if (typeName.equals("DOUBLE[]") || typeName.equals("FLOAT[]")) {
-          return Lists.DoubleList.class;
-        } else if (typeName.equals("LONG[]") || typeName.equals("HUGEINT[]") || typeName.equals("INT[]")) {
-          return Lists.LongList.class;
-        } else if (typeName.equals("DATE[]")) {
-          return Lists.LocalDateList.class;
-        } else {
-          return List.class; // we convert Array to List
-        }
-      }
-      return klass;
+      return sqlTypeToJavaClass(metaData.getColumnType(column), metaData.getColumnTypeName(column));
     } catch (SQLException e) {
       throw new RuntimeException(e);
     }
   }
+
+  public static Class<?> sqlTypeToJavaClass(int dataType, String columnTypeName) {
+    Class<?> klass = JdbcUtil.sqlTypeToClass(dataType);
+    // Special case for DuckDBColumnType.HUGEINT (128 bits integer). See also #getTypeValue
+    if (klass == Object.class) {
+      if (columnTypeName.equals(DuckDBColumnType.HUGEINT.name())) {
+        klass = long.class;
+      }
+    } else if (klass == List.class) {
+      if (columnTypeName.equals("DOUBLE[]") || columnTypeName.equals("FLOAT[]")) {
+        return Lists.DoubleList.class;
+      } else if (columnTypeName.equals("LONG[]") || columnTypeName.equals("HUGEINT[]") || columnTypeName.equals("INT[]") || columnTypeName.equals("INTEGER[]")) {
+        return Lists.LongList.class;
+      } else if (columnTypeName.equals("DATE[]")) {
+        return Lists.LocalDateList.class;
+      } else if (columnTypeName.equals("VARCHAR[]")) {
+        return Lists.StringList.class;
+      } else {
+        return List.class; // we convert Array to List
+      }
+    }
+    return klass;
+  }
 }
diff --git a/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java b/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java
index 11cb404..ac3109e 100644
--- a/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java
+++ b/duckdb/src/main/java/io/squashql/query/database/DuckDBQueryEngine.java
@@ -36,7 +36,7 @@ public class DuckDBQueryEngine extends JdbcQueryEngine<DuckDBDatastore> {
 
   @Override
   protected BiFunction<ResultSetMetaData, Integer, Class<?>> typeToClassConverter() {
-    return DuckDBUtil::sqlTypeToClass;
+    return DuckDBUtil::getColumnJavaClass;
   }
 
   @Override
diff --git a/js/typescript-library/src/condition.ts b/js/typescript-library/src/condition.ts
index 093225d..23007dc 100644
--- a/js/typescript-library/src/condition.ts
+++ b/js/typescript-library/src/condition.ts
@@ -23,6 +23,7 @@ export enum ConditionType {
   OR = "OR",
   NULL = "NULL",
   NOT_NULL = "NOT_NULL",
+  ARRAY_CONTAINS = "ARRAY_CONTAINS",
 }
 
 function toJSON(c: Condition) {
@@ -192,3 +193,10 @@ export function ge(value: Primitive): Condition {
 export function like(value: string): Condition {
   return new SingleValueCondition(ConditionType.LIKE, value)
 }
+
+/**
+ * Array contains condition.
+ */
+export function contains(value: any): Condition {
+  return new SingleValueCondition(ConditionType.ARRAY_CONTAINS, value)
+}
diff --git a/js/typescript-library/src/generate-json-queryDto.ts b/js/typescript-library/src/generate-json-queryDto.ts
index 50e974d..4588b51 100644
--- a/js/typescript-library/src/generate-json-queryDto.ts
+++ b/js/typescript-library/src/generate-json-queryDto.ts
@@ -19,7 +19,7 @@ import {
 import {
   _in,
   all,
-  and, ConditionType,
+  and, ConditionType, contains,
   criterion,
   criterion_,
   eq,
@@ -100,7 +100,8 @@ export function generateFromQueryDto() {
     criterion(new TableField("f2"), gt(659)),
     criterion(new TableField("f3"), _in([0, 1, 2])),
     criterion(new TableField("f4"), isNull()),
-    criterion(new TableField("f5"), isNotNull())
+    criterion(new TableField("f5"), isNotNull()),
+    criterion(new TableField("f6"), contains(2))
   ]))
 
   q.withHavingCriteria(all([
diff --git a/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java b/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java
index 332bea7..d7ffdba 100644
--- a/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java
+++ b/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java
@@ -36,7 +36,7 @@ public class SnowflakeDatastore implements JdbcDatastore {
     properties.put("db", database);
     properties.put("schema", schema);
     this.connectionProperties = properties;
-    this.stores = Suppliers.memoize(() -> JdbcUtil.getStores(database, schema, getConnection(), JdbcUtil::sqlTypeToClass));
+    this.stores = Suppliers.memoize(() -> JdbcUtil.getStores(database, schema, getConnection(), (dataType, __) -> JdbcUtil.sqlTypeToClass(dataType)));
   }
 
   @Override
diff --git a/spark/src/main/java/io/squashql/SparkUtil.java b/spark/src/main/java/io/squashql/SparkUtil.java
index ddf0888..6f73445 100644
--- a/spark/src/main/java/io/squashql/SparkUtil.java
+++ b/spark/src/main/java/io/squashql/SparkUtil.java
@@ -1,6 +1,7 @@
 package io.squashql;
 
 import io.squashql.jdbc.JdbcUtil;
+import io.squashql.list.Lists;
 import io.squashql.type.TableTypedField;
 import io.squashql.util.Types;
 import org.apache.spark.sql.types.ArrayType;
@@ -66,6 +67,10 @@ public final class SparkUtil {
       type = DataTypes.BooleanType;
     } else if (clazz.equals(LocalDate.class)) {
       type = DataTypes.DateType;
+    } else if (clazz.equals(Lists.LongList.class)) {
+      type = DataTypes.createArrayType(DataTypes.LongType);
+    } else if (clazz.equals(Lists.StringList.class)) {
+      type = DataTypes.createArrayType(DataTypes.StringType);
     } else {
       throw new IllegalArgumentException("Unsupported field type " + clazz);
     }


Test patch:
diff --git a/bigquery/src/test/java/io/squashql/query/TestBigQueryVector.java b/bigquery/src/test/java/io/squashql/query/TestBigQueryVector.java
new file mode 100644
index 0000000..9127d5f
--- /dev/null
+++ b/bigquery/src/test/java/io/squashql/query/TestBigQueryVector.java
@@ -0,0 +1,51 @@
+package io.squashql.query;
+
+import io.squashql.BigQueryDatastore;
+import io.squashql.BigQueryServiceAccountDatastore;
+import io.squashql.query.database.BigQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.BigQueryDataLoader;
+import io.squashql.transaction.DataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+import static io.squashql.query.BigQueryTestUtil.DATASET_NAME;
+import static io.squashql.query.BigQueryTestUtil.PROJECT_ID;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.BigQueryClassTemplateGenerator}.
+ */
+public class TestBigQueryVector extends ATestVector {
+
+  @AfterAll
+  void tearDown() {
+    this.fieldsByStore.forEach((store, fields) -> BigQueryTestUtil.deleteTable(((BigQueryDatastore) this.datastore).getBigquery(), store));
+  }
+
+  @Override
+  protected void createTables() {
+    BigQueryDataLoader tm = (BigQueryDataLoader) this.tm;
+    BigQueryTestUtil.createDatasetIfDoesNotExist(tm.getBigQuery(), DATASET_NAME);
+    this.fieldsByStore.forEach((store, fields) -> tm.dropAndCreateInMemoryTable(store, fields));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new BigQueryEngine((BigQueryDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new BigQueryServiceAccountDatastore(BigQueryTestUtil.createServiceAccountCredentials(), PROJECT_ID, DATASET_NAME);
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new BigQueryDataLoader(((BigQueryDatastore) this.datastore).getBigquery(), DATASET_NAME);
+  }
+
+  @Override
+  protected Object translate(Object o) {
+    return BigQueryTestUtil.translate(o);
+  }
+}
diff --git a/clickhouse/src/test/java/io/squashql/query/TestClickHouseVector.java b/clickhouse/src/test/java/io/squashql/query/TestClickHouseVector.java
new file mode 100644
index 0000000..5068688
--- /dev/null
+++ b/clickhouse/src/test/java/io/squashql/query/TestClickHouseVector.java
@@ -0,0 +1,53 @@
+package io.squashql.query;
+
+import io.squashql.ClickHouseDatastore;
+import io.squashql.query.database.ClickHouseQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.template.ClickHouseClassTemplateGenerator;
+import io.squashql.transaction.ClickHouseDataLoader;
+import io.squashql.transaction.DataLoader;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.BeforeAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link ClickHouseClassTemplateGenerator}.
+ */
+public class TestClickHouseVector extends ATestVector {
+
+  public org.testcontainers.containers.GenericContainer container = ClickHouseTestUtil.createClickHouseContainer();
+
+  @BeforeAll
+  @Override
+  void setup() {
+    this.container.start();
+    super.setup();
+  }
+
+  @AfterAll
+  void tearDown() {
+    // we do not stop the container to be able to reuse it between tests.
+    ((ClickHouseDataLoader) this.tm).dropTables(this.fieldsByStore.keySet());
+  }
+
+  @Override
+  protected void createTables() {
+    ClickHouseDataLoader tm = (ClickHouseDataLoader) this.tm;
+    this.fieldsByStore.forEach((store, fields) -> tm.dropAndCreateInMemoryTable(store, fields));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new ClickHouseQueryEngine((ClickHouseDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new ClickHouseDatastore(ClickHouseTestUtil.jdbcUrl.apply(this.container));
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new ClickHouseDataLoader(((ClickHouseDatastore) this.datastore).dataSource);
+  }
+}
diff --git a/core/src/test/java/io/squashql/query/ATestVector.java b/core/src/test/java/io/squashql/query/ATestVector.java
new file mode 100644
index 0000000..0be1a16
--- /dev/null
+++ b/core/src/test/java/io/squashql/query/ATestVector.java
@@ -0,0 +1,71 @@
+package io.squashql.query;
+
+import io.squashql.TestClass;
+import io.squashql.list.Lists;
+import io.squashql.query.builder.Query;
+import io.squashql.query.dto.QueryDto;
+import io.squashql.table.Table;
+import io.squashql.type.TableTypedField;
+import org.assertj.core.api.Assertions;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.TestInstance;
+
+import java.util.List;
+import java.util.Map;
+
+import static io.squashql.query.Functions.*;
+
+@TestClass(ignore = TestClass.Type.SNOWFLAKE)
+@TestInstance(TestInstance.Lifecycle.PER_CLASS)
+public abstract class ATestVector extends ABaseTestQuery {
+  final String storeName = "mystore" + getClass().getSimpleName().toLowerCase();
+  final Field tag = new TableField(this.storeName, "tag");
+  final Field tagSt = new TableField(this.storeName, "tagSt");
+  final Field ean = new TableField(this.storeName, "ean");
+  final Field price = new TableField(this.storeName, "price");
+
+  @Override
+  protected Map<String, List<TableTypedField>> getFieldsByStore() {
+    TableTypedField ean = new TableTypedField(this.storeName, "ean", String.class);
+    TableTypedField price = new TableTypedField(this.storeName, "price", double.class);
+    TableTypedField tag = new TableTypedField(this.storeName, "tag", Lists.LongList.class);
+    TableTypedField tagSt = new TableTypedField(this.storeName, "tagSt", Lists.StringList.class);
+    return Map.of(this.storeName, List.of(ean, price, tag, tagSt));
+  }
+
+  @Override
+  protected void loadData() {
+    this.tm.load(this.storeName, List.of(
+            new Object[]{"A", 10d, List.of(1L, 3L, 5L), List.of("1", "3", "5")},
+            new Object[]{"B", 8d, List.of(1L, 2L), List.of("1", "2")},
+            new Object[]{"C", 2d, List.of(2L, 4L, 6L), List.of("2", "4", "6")},
+            new Object[]{"D", 1d, List.of(1L), List.of("1")}
+    ));
+  }
+
+  @Test
+  void testArrayContainsNumber() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion(this.tag, contains(2)))
+            .select(List.of(this.ean), List.of(sum("price", this.price)))
+            .build();
+    Table table = this.executor.executeQuery(query);
+    Assertions.assertThat(table).containsExactly(
+            List.of("B", 8d),
+            List.of("C", 2d));
+  }
+
+  @Test
+  void testArrayContainsString() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion(this.tagSt, contains("2")))
+            .select(List.of(this.ean), List.of(sum("price", this.price)))
+            .build();
+    Table table = this.executor.executeQuery(query);
+    Assertions.assertThat(table).containsExactly(
+            List.of("B", 8d),
+            List.of("C", 2d));
+  }
+}
diff --git a/core/src/test/java/io/squashql/query/ATestVectorOperation.java b/core/src/test/java/io/squashql/query/ATestVectorOperation.java
index 18b1734..dbd9186 100644
--- a/core/src/test/java/io/squashql/query/ATestVectorOperation.java
+++ b/core/src/test/java/io/squashql/query/ATestVectorOperation.java
@@ -40,7 +40,7 @@ public abstract class ATestVectorOperation extends ABaseTestQuery {
   static final String competitorZ = "Z";
   static final int day = 5;
   static final int month = 4;
-  final String storeName = "mystore";// + getClass().getSimpleName().toLowerCase();
+  final String storeName = "mystore" + getClass().getSimpleName().toLowerCase();
   final Field competitor = new TableField(this.storeName, "competitor");
   final Field ean = new TableField(this.storeName, "ean");
   final Field price = new TableField(this.storeName, "price");
diff --git a/duckdb/src/test/java/io/squashql/query/TestDuckDBVector.java b/duckdb/src/test/java/io/squashql/query/TestDuckDBVector.java
new file mode 100644
index 0000000..d8245e3
--- /dev/null
+++ b/duckdb/src/test/java/io/squashql/query/TestDuckDBVector.java
@@ -0,0 +1,35 @@
+package io.squashql.query;
+
+import io.squashql.DuckDBDatastore;
+import io.squashql.query.database.DuckDBQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.DuckDBDataLoader;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.DuckDBClassTemplateGenerator}.
+ */
+public class TestDuckDBVector extends ATestVector {
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new DuckDBQueryEngine((DuckDBDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new DuckDBDatastore();
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new DuckDBDataLoader((DuckDBDatastore) this.datastore);
+  }
+
+  @Override
+  protected void createTables() {
+    DuckDBDataLoader tm = (DuckDBDataLoader) this.tm;
+    this.fieldsByStore.forEach(tm::createOrReplaceTable);
+  }
+}
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index 6c7f1f8..50d3b48 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -102,6 +102,7 @@ public class TestJavascriptLibrary {
     q.withCondition(tableField("f3"), in(0, 1, 2));
     q.withCondition(tableField("f4"), isNull());
     q.withCondition(tableField("f5"), isNotNull());
+    q.withCondition(tableField("f6"), contains(2));
 
     q.withHavingCriteria(all(criterion(price, ge(10)), criterion(expression, lt(100))));
 
diff --git a/spark/src/test/java/io/squashql/query/TestSparkVector.java b/spark/src/test/java/io/squashql/query/TestSparkVector.java
new file mode 100644
index 0000000..eaa3e56
--- /dev/null
+++ b/spark/src/test/java/io/squashql/query/TestSparkVector.java
@@ -0,0 +1,42 @@
+package io.squashql.query;
+
+import io.squashql.SparkDatastore;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.query.database.SparkQueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.SparkDataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SparkClassTemplateGenerator}.
+ */
+public class TestSparkVector extends ATestVector {
+
+  @AfterAll
+  void tearDown() {
+    this.fieldsByStore.keySet().forEach(storeName -> ((SparkDatastore) this.datastore).spark.catalog().dropTempView(storeName));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new SparkQueryEngine((SparkDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new SparkDatastore();
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    SparkDatastore ds = (SparkDatastore) this.datastore;
+    return new SparkDataLoader(ds.spark);
+  }
+
+  @Override
+  protected void createTables() {
+    SparkDataLoader tm = (SparkDataLoader) this.tm;
+    this.fieldsByStore.forEach((store, fields) -> tm.createTemporaryTable(store, fields));
+  }
+}