Scala, Quill - how to use SQL IN clause in quill query?

Question

I have a database, which model looks like:

case class Data {
  id: Int, 
  value: Json
}

I would like to create search query which can filter some fields from value JSON by csv:

def search(id: Int) = {
  ....
  val list = List("value1", "value2", "value3")
  quote(
    myTable.filter( t => t.id == lift(id)))
    .dynamic
    .filter(_ => liftQuery(list).contains(infix""" t.name """.pure.as[Boolean]))
}

The problem is it does not work well. I would have no idea how I could use IN clause here in other way. value is a JSON which include field name and now I would like to search all elements in myTable which fit to values pass in list. I tried also do something like:

infix""" t.name in ${list.mkString(",")} """.pure.as[Boolean]

But it also did not work. Can you help me with some solution? There is no good example of IN clause in quill docs.

Answer 1

quote(query[Data].filter(d => liftQuery(list).contains(infix"value->>'name'")))

results in:

SELECT d.id, d.value FROM data d WHERE value->>'name' IN (?, ?, ?)

https://scastie.scala-lang.org/t4YItAweTna6P5dEw1JRTg