Slick plain SQL escape PostgreSQL json function

Question

I'm trying to escape the ?| operator in this query:

val data = sql"""
  SELECT ......
  FROM .......
  WHERE table.column ?| array['23', '12']
""".as[Int].head

db.run(data)

however the ?| operator gets translated as $1| in the query (checked in the DB query log) and it obviously generates the error

ERROR:  syntax error at or near "$1" at character 735

I've tried with #?| and $?| without success

Richard Dallaway Richard Dallaway · Accepted Answer · 2018-01-18T09:36:27

? is a placeholder for a parameter in JDBC (that's the level after Slick). You can escape ? specifically for PostgreSQL as ??|. There's useful discussion of this in SO 14779896 - Does the JDBC spec prevent '?' from being used as an operator.

An alternative to this convention would be to use non-symbolic alternatives: jsonb_exists_any. E.g.,

WHERE jsonb_exists_any(table.column, array['23', '12'])