Gorm: Way to escape Question Mark in Raw Query?

[boonep]

For the jsonb field type in PostgreSQL 9.4, the question mark '?' is used as an operator: http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE

When trying to implement a raw query, gorm attempts to replace the question mark with a value. Is there any way to escape the question mark, so I could do something like:

i.DB.Raw(SELECT * FROM json_test WHERE data \? 'a';)

(Where the backslash is escaping the question mark, so it isn't replaced?) Thanks!

[vpol]

Is there any progress on it?

[boonep]

Not that I've heard. I'm more than happy to implement something if @jinzhu wants to provide some direction on what format we should implement for escaping. Thanks!

[pedromorgan]

Can we fling in wkB and postgis in this arguments..

I love it BTW from @peedromorgan land

[moberemk]

I've also run into this actually; my first thought was to try the backslash so +1 to that suggestion as an escaping character.

For the curious, in my use case I managed to replace it by extracting the JSON value of the key (->'KEY') and checking IS NOT NULL. That won't solve the problem for cases where NULL is a valid value though.

[bbstk]

I was looking through the code and found this function that is used when preparing a query:
func (scope *Scope) Raw(sql string) *Scope { scope.SQL = strings.Replace(sql, "$$$", "?", -1) return scope }

I used $$$ in order to get the question mark operator and it does the trick. The above example will look like this:
i.DB.Raw(SELECT * FROM json_test WHERE data $$$ 'a';)

However, I am not sure if this is the intended way of using $$$. What do you think about this solution?

[Xzya]

@jinzhu Any update on this? Is @bbstk solution intended or is it possible to break in the future? Thanks!

[nmec]

For anyone still having this issue, I found a workaround by using the functional equivalent of the ? operator which is jsonb_exists(jsonb, text).

You can find the functional equivalent of an operator with the following query if you need to use other operators such as ?| or ?&:

SELECT 
  oprname, 
  oprcode || '(' || format_type(oprleft,  NULL::integer) || ', ' 
                 || format_type(oprright, NULL::integer) || ')' AS function
FROM pg_operator 
WHERE oprname = '?';

[hdm]

Note that using the json* functions will NOT use the index.

[cchengubnt]

I found a way to keep ? in sql using gorm.Expr("?")
for example:

db.Where("data::jsonb ? ?", gorm.Expr("?"), name)

// select * from table_x where data::jsonb ? 'a'

[hdm]

For folks using sqlx directly (or its wrappers), creating this alias avoids the ? issue while still using the index.

CREATE OR REPLACE FUNCTION jsonb_exists_indexed(jsonb, text) RETURNS bool AS E'SELECT $1 \x3F $2' LANGUAGE sql IMMUTABLE;

Use this as jsonb_exists_indexed(col, 'value') to get index-friendly lookups without a literal ?.

[jinzhu]

Follow https://github.com/go-gorm/datatypes for how to use JSON with Gorm v2.