how to translate update set SQL to scala quill?

Question

I have a schema in PostgreSql in which I want do update set for users_id field:

CREATE TABLE if not exists rooms (
  oid char(24),
  owner_id char(24) not null,
  users_id text[],

  PRIMARY KEY (oid)
);

The execute sql as this:

update rooms set users_id = (select array_agg(distinct e) from  
unnest(users_id || '{5a16f7ce77c8a2b22406fb86}') e) where  oid =  
'5a16f7ce77c8a2b22406fb86';

it update users_id array filed and do distinct operation.

In Quill, I have attempt with the method:

def addUserInRoom(userId: ObjectId, roomId: ObjectId): Unit = {
    val q = quote(
      (uid: String, rid: String) =>
        infix"""update rooms set users_id = (select array_agg(distinct e) from unnest(users_id || '{${uid}}') e) where  oid = '${rid}'""".as[Query[Long]]
    )

    run(q(lift(userId.toString), lift(roomId.toString)))
}

A exception occurred:

Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128)
    at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1029)
    at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:353)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java)
    at io.getquill.context.jdbc.Encoders.$anonfun$stringEncoder$2(Encoders.scala:44)
    at io.getquill.context.jdbc.Encoders.$anonfun$stringEncoder$2$adapted(Encoders.scala:44)
...

How can I execute the sql with scala Quill library? Different way always welcome!
Thanks

Update - more information

dependency is :

  "org.postgresql" % "postgresql" % "42.1.4",
  "io.getquill" %% "quill-jdbc" % "2.2.0",

My driver instance is:

lazy val ctx = new PostgresJdbcContext(
    NamingStrategy(SnakeCase, PostgresEscape),
    AppConfig.quill
  )

Besides, some simple sql has test successful.

Answer 1

  lazy val ctx = new PostgresJdbcContext(SnakeCase, "ctx")
  import ctx._

  case class Rooms(oid: String, ownerId: String, usersId: Seq[String])

  def foo(oid: String, uid: String) = {
    val uids: Seq[String] = Seq(uid)
    val v = quote(infix"(select array_agg(distinct e) from unnest(users_id || ${lift(uids)}) e)".as[Seq[String]])

    val q = quote {
      query[Rooms].filter(_.oid == lift(oid))
        .update(_.usersId -> unquote(v))
    }
    ctx.run(q)
  }
  def main(args: Array[String]): Unit = {
    println(foo("1", "2"))
  }