:wave: hey team, I am actively updating from a sig...

# exposed

[Andres]

👋 hey team, I am actively updating from a significantly older version where I had extensions that I shouldn't need anymore. I am trying to use the new array column but I am struggling to understand how I could do a containsAny or overlaps.

ex:

val listColumn: Column<List<String>> = array<String>("list_column")

.where {
    Table.listColumn containsAny listOf("a', "b")
}

Am I missing something or is this not possible currently without an extension?

[Andres]

I ended up re-adding my extension:

import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Expression
import org.jetbrains.exposed.sql.ExpressionWithColumnType
import org.jetbrains.exposed.sql.Op
import org.jetbrains.exposed.sql.QueryBuilder
class ArrayContainsAnyOp<T>(
    private val column: Expression<T>,
    private val elements: Iterable<*>,
) : Op<Boolean>() {

    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder {
            val columnName = (column as? Column<*>)?.name ?: column.toString().substringAfterLast('.')
            append(elements.joinToString(" OR ") { "'$it' = ANY($columnName)" })
        }
}

infix fun <T> ExpressionWithColumnType<T>.containsAny(iterable: Iterable<*>) = ArrayContainsAnyOp(this, iterable)

But still curious if there is a better way.

[Chantal Loncle]

Hi @Andres The new array() column type does not support every single existing array operator or function, but one of the supported options is ANY() (and ALL()), via anyFrom() and allFrom(). They both accept multiple argument types (like subqueries, collections, columns, tables).

You can use the preceding comparison operator of your choice to build the clause, for example:

Table
    .selectAll()
    .where {
        Table.listColumn eq anyFrom(listOf("a", "b"))
    }

// generates SQL:
// ... WHERE list_column = ANY(ARRAY['a','b'])

More info about the array type and its supported functions can be found here in the docs.

[Andres]

Hmm I actually tried anyFrom but I was getting the following error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying[] = text\n  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.\n  Position: 201

[Chantal Loncle]

@Andres Could you confirm what is on the left- and right-side of the operator?
If the left is a stored array of strings, and the right is a collection of individual strings, please share the expected SQL that would accomplish the comparison you want using ANY().
The SQL I shared previously would not be valid if the left-side stores a collection of data.

If that is the case, you could use anyFrom() with one of our compound operators, something like:

val stringsOverlap = listOf("C", "D").map { str ->
    Op.build { stringParam(str) eq anyFrom(TestTable.strings) }
}.compoundOr()

TestTable
    .selectAll()
    .where { stringsOverlap }

// generates SQL like:
// ... WHERE ('C' = ANY (tester.strings)) OR ('D' = ANY (tester.strings))

If that doesn't get the results you need, is it possible that the operator you want is PostgreSQL's &&? It's not currently supported out-of-the-box, so here's the implementation in case:

class OverlapOp(
    val target: Expression<*>,
    val candidate: Expression<*>
) : Op<Boolean>(), ComplexExpression {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append(target, " && ", candidate)
    }
}

inline infix fun <reified T : Any, L : List<T>> ExpressionWithColumnType<L>.overlap(
    collection: L
) =
    OverlapOp(this, arrayLiteral(collection))

TestTable
    .selectAll()
    .where {
        TestTable.strings overlap listOf("C", "D")
    }

// generates SQL like:
// ... WHERE tester.strings && ARRAY['C','D']

[Andres]

Hmm maybe it is me sad panda

last_known_related_camera_serial_numbers character varying[]

SELECT * FROM monitorstatusdetails WHERE last_known_related_camera_serial_numbers = ANY(ARRAY['6d', 'o8'])
[2025-01-24 14:41:43] [42883] ERROR: operator does not exist: character varying[] = text
[2025-01-24 14:41:43] Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
[2025-01-24 14:41:43] Position: 83

[Andres]

ALTER TABLE monitorstatusdetails ADD COLUMN last_known_related_camera_serial_numbers VARCHAR ARRAY;

Is it because I am using VARCHAR? I wont pretend I am any kind of database expert

[Chantal Loncle]

No worries, it's the code I suggested that isn't right for your use case.
Your left-side operand is of type ARRAY so the right-side operand would need to be nested arrays, which is not what you need.

Please try the suggestion from the first code block above, using anyFrom() with compoundOr(). That should work for you.

[Andres]

compoundOr seems to want a Boolean?

public fun List<Op<Boolean>>.compoundOr(): Op<Boolean> defined in org.jetbrains.exposed.sql

[Chantal Loncle]

Yes that's correct, so you build a boolean operator for each element in the collection and compound the resulting list of booleans.
Here's the code block I meant:

val stringsOverlap = listOf("C", "D").map { str ->
    Op.build { stringParam(str) eq anyFrom(TestTable.strings) }
}.compoundOr()

TestTable
    .selectAll()
    .where { stringsOverlap }

// generates SQL like:
// ... WHERE ('C' = ANY (tester.strings)) OR ('D' = ANY (tester.strings))

[Andres]

Got it let me give that a try in a few

[Andres]

This worked great btw, I ended up making a little helper

fun <T> containsAny(
    column: Column<List<T>>,
    items: List<T>,
) = items.map { item ->
    Op.build { stringParam(item.toString()) eq anyFrom(column) }
}.compoundOr()