One of the things I really like about Postgres is the various types of columns you can use (without saying that this is limited to Postgres). In this context, the array column type makes it easy to store a list of “stuff”. You can then use the various functions that PostgreSQL provides to query and transform those data. Recently I needed to get all the unique elements from all the values of an array column and once it worked, I pushed a little further to achieve the same result with Exposed.

PostgreSQL’s Array Type

In the name of everything that is holy and pure in this world, I implore you not to quote me on this, but my understanding is that the array type allows you to store a list of any available types, built-in and user-defined all the same. That said, in this example, we’re going to use a simple list of strings. This is what our table looks like:

CREATE TABLE books (
    name text,
    genres text[]
);

We’re also going to add a few books, like so:

INSERT INTO "books"("name", "genres") VALUES('When Women Where Dragons', '{Romance,Fantasy,Feminism}');
INSERT INTO "books"("name", "genres") VALUES('Pride and Prejudice', '{Romance,Classics}');
INSERT INTO "books"("name", "genres") VALUES('Salt: A World History', '{Food,Science}');

Getting a list of all the genres

Once we have a list of genres stored into the books table, getting a (distinct, ideally) list of genres means expanding the “genres” array of each row into a set of rows, using the unnest function. Since we want a unique list of genres, we throw a distinct into the mix, which gives us the following query:

SELECT DISTINCT unnest(genres) genre FROM books ORDER BY genre

After running this query, we get 6 rows, one for each genre:

  genre   
----------
 Fantasy
 Food
 Romance
 Science
 Feminism
 Classics
(6 rows)

Exposed does not support most of the array functions for postgres. Fortunately, it’s fairly trivial to add custom functions.

Adding support in Exposed

First, let’s define the table using Exposed:

object Books: Table("books") {
    val name = varchar("name", 255)
    val genres = array<String>("genres")
}

There are many ways to create custom functions with exposed but in this case, I’ve found that the easiest way is to extend the CustomFunction class:

class Unnest<T>(
    expr: Column<List<T>>,
    column: IColumnType<T & Any>,
): CustomFunction<T>("UNNEST", column, expr)

Then, create an helper function to make it easy to use it in a query (as long as the column is an array of String, otherwise you need adapt it):

fun Column<List<String>>.unnest() = Unnest(this, TextColumnType())

val genres = BookTable.select(Books.genres.unnest()).withDistinct(true).map { it[Books.genres.unnest()] }

Conclusion

In situations like these, I originally reverted to using raw SQL queries, but having gained a little more knowledge about the internals of Exposed, adding extensions makes the codebase a little nicer and more consistent.