Array

Exposed provides support for array data types, allowing you to store and manipulate arrays of various data types in your database. PostgreSQL and H2 databases support explicit array types, with PostgreSQL offering additional support for multidimensional arrays.

Supported types

The exposed-core module supports two main ways to define array columns using the .array() function:

Basic array definition using type inference for supported types:
val memberIds = array<UUID>("member_ids")
Array definition with explicit column type for more control or custom types:
val deadlines = array<LocalDate>("deadlines", KotlinLocalDateColumnType()).nullable()
Using explicit column types prevents exceptions when Exposed cannot automatically determine the appropriate mapping.

Basic usage

Array columns store and retrieve data as Kotlin List objects, providing a natural way to work with array data in your code.

You can define array columns within a table definition as follows:

    // Simple array table definition
    object SimpleArrays : Table("teams") {
        val memberIds = array<UUID>("member_ids")
        val memberNames = array<String>("member_names")
        val budgets = array<Double>("budgets")
    }

    // Array with explicit column type table definition
    object AdvancedArrays : Table("teams") {
        val memberNames = array<String>("member_names", VarCharColumnType(colLength = MEMBER_NAME_LENGTH))
        val deadlines = array<LocalDate>("deadlines", KotlinLocalDateColumnType()).nullable()
        val expenses = array<Double?>("expenses", DoubleColumnType()).default(emptyList())
    }

Here's an example of inserting data into array columns:

            SimpleArrays.insert {
                it[memberIds] = List(TEAM_SIZE) { UUID.randomUUID() }
                it[memberNames] = List(TEAM_SIZE) { i -> "Member ${('A' + i)}" }
                it[budgets] = listOf(DEFAULT_BUDGET)
            }

Multidimensional arrays

PostgreSQL supports multidimensional arrays, which can be defined using the dimensions parameter:

    // Multidimensional array table definition
    object MultiDimArrays : Table("teams") {
        val nestedMemberIds = array<UUID, List<List<UUID>>>(
            "nested_member_ids", dimensions = 2
        )
        val hierarchicalMemberNames = array<String, List<List<List<String>>>>(
            "hierarchical_member_names",
            VarCharColumnType(colLength = MEMBER_NAME_LENGTH),
            dimensions = 3
        )
    }

Array indexing

To access individual elements, use index operators:

            val firstMember = SimpleArrays.memberIds[FIRST_MEMBER_INDEX]
            SimpleArrays
                .select(firstMember)
                .where { SimpleArrays.budgets[FIRST_MEMBER_INDEX] greater MIN_BUDGET }

Array slicing

Use .slice() to extract subarrays:

            SimpleArrays.select(SimpleArrays.memberNames.slice(SLICE_START, SLICE_END_SMALL))

Both lower and upper bounds are optional when using PostgreSQL.

ANY and ALL operators

Use array columns with SQL's ANY and ALL operators:

            SimpleArrays
                .selectAll()
                .where { SimpleArrays.budgets[FIRST_MEMBER_INDEX] lessEq allFrom(SimpleArrays.budgets) }

            SimpleArrays
                .selectAll()
                .where { stringParam("Member A") eq anyFrom(SimpleArrays.memberNames.slice(SLICE_START, SLICE_END_LARGE)) }

Last modified: 03 July 2025