hoping someone can point me in the right place: I’m trying a jsonb existence query select * from users u where id = :id and permissions::jsonb ? :permission-string where permissions is an array of strings. Hugsql flips out a bit with thinking there are 3 parameters, per this sqlvec: ["SELECT\n u.id\nFROM USERS u\nWHERE id = ?\n AND permissions::jsonb ? ?" "an-id" "write"]. Is there a way to escape that ? … the JSONB existence operator in postgres?

Note that select * from users u, jsonb_array_elements(u.permissions) permission where id = 'an-id' and permission.value::TEXT = '"write"' can work too, but that last double-quoting is a pain.

@rgm Try escaping the ? with a ??. “In JDBC, the question mark (`?`) is the placeholder for the positional parameters of a `PreparedStatement`. There are, however, a number of PostgreSQL operators that contain a question mark. To keep such question marks in a SQL statement from being interpreted as positional parameters, use two question marks (`??`) as escape sequence.” Per this page: https://jdbc.postgresql.org/documentation/head/statement.html

oh wow, thanks. I’ll give that a go.

(think psql might need that “hard to google” explanation page).

(or I should actually read the JSON docs cover to cover some day… also a idea).

Is it possible to pass the field name from Clojure? WHERE XXX = 1. I have too many select by email, select by id, select by username funcs now... it would be simpler to be able to have WHERE dynamic somehow