Hello Exposed Community, I'm currently facing an ...

# exposed

[Dirk]

Hello Exposed Community,

I'm currently facing an issue where a custom function I wrote is not appearing in the SQL WHERE clause as I intended. The function, called JsonValue2, is designed to extract JSON values from a database column. Here's the class definition:

class JsonValue2<T>(private val column: Expression<*>, override val columnType: IColumnType, private val path: String) : Function<T>(columnType) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        <http://log.info|log.info>("-----> JsonValue shall be generated. column: $column, path: $path, columnType: $columnType")
        append("JSON_VALUE(")
        column.toQueryBuilder(queryBuilder)
        append(", '$")
        append(path)
        append("')")
    }
}

I'm attempting to use this function in a select statement as follows:

Job
    .select {
        (Job.userID eq uid) and
        (JsonValue2<SitzungsId>(Job.daten, SitzungsIDColumnType(), "konfiguration.sitzungsID") inList sitzungsIDs) and
        (RowNum eq 1L)
    }

However, when inspecting the generated output, the function call does not appear in the WHERE clause, and the result only shows FROM JOB WHERE ROWNUM = 1.

Could you provide some insights as to why my JsonValue2 function isn't being included in the generated SQL statement? I'm not sure where I've gone wrong.

Thanks in advance for your help!

[Dominik Sandjaja]

Does the .userID eq uid part appear in the generated SQL?

[Dirk]

Wow thank you very much. I had shortened the query here. In order to be able to post the complete query here, I had now shortened the query and thus also removed the first attempt that was still contained in the query. And wait and see... It works. In this respect, many thanks for the impulse!