Array Operations

Learn how to work with array columns in DuckDB, including the differences from Postgres.

Array Types

DuckDB has two array-like types:

LIST (Variable Length)

import { duckDbList } from '@leonardovida-md/drizzle-neo-duckdb';

const users = pgTable('users', {
  tags: duckDbList<string>('tags', 'TEXT'),
  scores: duckDbList<number>('scores', 'INTEGER'),
});

ARRAY (Fixed Length)

import { duckDbArray } from '@leonardovida-md/drizzle-neo-duckdb';

const users = pgTable('users', {
  rgb: duckDbArray<number>('rgb', 'INTEGER', 3),
  coordinates: duckDbArray<number>('coordinates', 'DOUBLE', 2),
});

Inserting Array Data

await db.insert(users).values({
  tags: ['typescript', 'drizzle', 'duckdb'],
  scores: [85, 92, 78],
});

Arrays are returned as native JavaScript arrays:

const [user] = await db.select().from(users);
console.log(user.tags); // ['typescript', 'drizzle', 'duckdb']

Querying Arrays

Using DuckDB Helpers (Recommended)

import {
  duckDbArrayContains,
  duckDbArrayContained,
  duckDbArrayOverlaps,
} from '@leonardovida-md/drizzle-neo-duckdb';

duckDbArrayContains

Check if array contains all specified values:

// Find users with BOTH 'admin' AND 'verified' tags
const admins = await db
  .select()
  .from(users)
  .where(duckDbArrayContains(users.tags, ['admin', 'verified']));

Generated SQL:

SELECT * FROM users WHERE array_has_all(tags, ['admin', 'verified'])

duckDbArrayContained

Check if array is contained by the specified values:

// Find users whose tags are ALL within ['basic', 'standard', 'premium']
const regularUsers = await db
  .select()
  .from(users)
  .where(duckDbArrayContained(users.tags, ['basic', 'standard', 'premium']));

Generated SQL:

SELECT * FROM users WHERE array_has_all(['basic', 'standard', 'premium'], tags)

duckDbArrayOverlaps

Check if arrays have any common elements:

// Find users with ANY of these tags
const specialUsers = await db
  .select()
  .from(users)
  .where(
    duckDbArrayOverlaps(users.tags, ['vip', 'beta-tester', 'early-adopter'])
  );

Generated SQL:

SELECT * FROM users WHERE array_has_any(tags, ['vip', 'beta-tester', 'early-adopter'])

Automatic Operator Rewriting

Drizzle DuckDB automatically rewrites Postgres array operators using AST transformation:

Postgres	DuckDB Equivalent
@>	array_has_all(column, values)
<@	array_has_all(values, column)
&&	array_has_any(column, values)

This means Postgres-style code works seamlessly:

import { arrayContains } from 'drizzle-orm/pg-core';

// This is automatically rewritten to DuckDB syntax
const results = await db
  .select()
  .from(users)
  .where(arrayContains(users.tags, ['admin']));

Combining Array Conditions

import { and, or } from 'drizzle-orm';

// Users with premium tag AND (vip OR early-adopter)
const premiumUsers = await db
  .select()
  .from(users)
  .where(
    and(
      duckDbArrayContains(users.tags, ['premium']),
      duckDbArrayOverlaps(users.tags, ['vip', 'early-adopter'])
    )
  );

// Users with admin permissions OR moderator permissions
const privilegedUsers = await db
  .select()
  .from(users)
  .where(
    or(
      duckDbArrayOverlaps(users.permissions, ['admin', 'super-admin']),
      duckDbArrayContains(users.permissions, ['moderator'])
    )
  );

Array Functions in Raw SQL

DuckDB has many array functions available via raw SQL:

import { sql } from 'drizzle-orm';

// Array length
const result = await db.execute(sql`
  SELECT name, array_length(tags) as tag_count
  FROM users
  WHERE array_length(tags) > 3
`);

// Array element access (1-indexed)
const result = await db.execute(sql`
  SELECT name, tags[1] as first_tag
  FROM users
`);

// Array aggregation
const result = await db.execute(sql`
  SELECT user_id, array_agg(tag) as all_tags
  FROM user_tags
  GROUP BY user_id
`);

// Unnest arrays
const result = await db.execute(sql`
  SELECT name, unnest(tags) as tag
  FROM users
`);

// Array concatenation
const result = await db.execute(sql`
  SELECT array_concat(tags, ['new-tag']) as updated_tags
  FROM users
`);

Common Patterns

Filter by Multiple Tags (AND)

// Users who have ALL of these tags
const powerUsers = await db
  .select()
  .from(users)
  .where(duckDbArrayContains(users.tags, ['verified', 'premium', 'active']));

Filter by Any Tag (OR)

// Users who have ANY of these tags
const targetUsers = await db
  .select()
  .from(users)
  .where(duckDbArrayOverlaps(users.tags, ['marketing', 'sales', 'support']));

Check Array Not Empty

const usersWithTags = await db.execute(sql`
  SELECT * FROM users WHERE array_length(tags) > 0
`);

Check Specific Element Exists

const admins = await db.execute(sql`
  SELECT * FROM users WHERE list_contains(tags, 'admin')
`);

Postgres Array Literal Warning

If you use Postgres-style array literals ('{a,b,c}'), you’ll see a warning:

// This triggers a warning
await db.execute(sql`SELECT * FROM users WHERE tags = '{a,b,c}'`);
// Warning: Postgres-style array literals are not supported

To make this a hard error:

const db = drizzle(connection, {
  rejectStringArrayLiterals: true,
});

Use native JavaScript arrays instead:

// Correct
await db.execute(sql`SELECT * FROM users WHERE tags = ['a', 'b', 'c']`);

See Also

• Array Helpers - API reference
• Column Types - LIST and ARRAY types
• Limitations - Array operator differences