Gorm Way to escape Question Mark in Raw Query?

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!

Comment From: vpol

Is there any progress on it?

Comment From: 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!

Comment From: pedromorgan

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

I love it BTW from @peedromorgan land

Comment From: 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.

Comment From: 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?

Comment From: Xzya

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

Comment From: 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 = '?';

Comment From: hdm

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

Comment From: 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'

Comment From: 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 ?.

Comment From: jinzhu

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

Comment From: vivere-dally

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' ```

This approach works well, it should be indexed more or added to the docs because lots of resources and first links while searching lead to escaping using \\ or ?? and none of those work.