How can I add a query by array index in scala slick?

Question

Using scala slick, how would you generate a query filter using plain text? Context - I'm trying to filter on an array position column, doesn't appear to be supported by the slick extensions provided by https://github.com/tminglei/slick-pg.

I.e. we have an array field:

   id UUID,
   name varchar,
   my_array_field varchar[],
   ...

And I'd like to be able to query by the sql equivalent of SELECT FROM BLAH where name='somename' and my_array_field[2] = 'bar'

• Is there a way to just inject a text string as a filter? i.e. not the slick sql interpolator for a query, but just a filter element?
• Is there a slick or slick-pg representation of retrieving by an array element?

Answer 1

In the README of slick-pg you can see at the usage section an example of how to use the lib. You would need to mix PgArraySupport to the profile and ArrayImplicits to the API.

trait MyPostgresProfile extends ExPostgresProfile
                           with PgArraySupport

  override val api = MyAPI

  object MyAPI extends ExtPostgresAPI with ArrayImplicits
}

object MyPostgresProfile extends MyPostgresProfile

Then you can see the array column defined as def tags = column[List[String]]("tags_arr") and the method byTag using a filter .filter(_.tags @& tags.toList.bind).

import MyPostgresProfile.api._

class TestTable(tag: Tag) extends Table[Test](tag, Some("xxx"), "Test") {
  def tags = column[List[String]]("tags_arr")
  def * = (tags) <> (Test.tupled, Test.unapply)
}

object tests extends TableQuery(new TestTable(_)) {
  // will generate sql like:
  //   select * from test where tags && ?
  def byTag(tags: String*) = tests
    .filter(_.tags @& tags.toList.bind)
    .map(t => t)
}

There is also a README - Supported Array Oper/Functions you can check.