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 · Accepted Answer · 2018-01-18T16:42:56.550

1

? 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'])

edited Jan 18 '18 at 16:42

answered Jan 18 '18 at 09:36

Richard Dallaway

I'me very sorry, I've made so many changes that I've copied the last try instead of the original one, I've updated the question – alex88 Jan 18 '18 at 14:43
I found that I need to use `??` *not* `??|` – Howy Perrin Aug 06 '18 at 10:01

1 Answers1