How to escape the ? (question mark) operator to query Postgresql JSONB type in Rails

22
votes

I'm working with Rails 4.2 and Postgres 9.4 to try out the new JSONB data type. One of the JSONB columns in my database holds an array, and I want to be able to query for records where this array contains a certain value. I figured out how to do this using the new JSONB "question mark" ("contains") operator, as documented here: http://www.postgresql.org/docs/9.4/static/functions-json.html

So in raw SQL I can get this to work as in this example:

SELECT * FROM people WHERE roles ? '32486d83-4a38-42ba-afdb-f77ca40ea1fc';

But I can't see any way to do this query from within Rails via ActiveRecord. I've tried doing a raw query using the "where" method, as follows:

Person.where("roles ? ?", "32486d83-4a38-42ba-afdb-f77ca40ea1fc")

But since the question mark is a special character used to replace parameters, I get this error:

ActiveRecord::PreparedStatementInvalid: wrong number of bind variables (1 for 2) in: roles ? ?

I guess I need a way to escape the "?" character since I want it to pass through literally. I've tried \? and ?? with no luck. Any help is appreciated!

2
ruby-on-railspostgresqlactiverecordjsonb
try this Person.where("roles ? (?)", "32486d83-4a38-42ba-afdb-f77ca40ea1fc") - Arup Rakshit
or Person.where("roles ? :name", name: "32486d83-4a38-42ba-afdb-f77ca40ea1fc") - Arup Rakshit
@R_O_R You know you can use to_sql to see what a query produces without actually running the query. - mu is too short
@muistooshort I checked Person.where("roles ? :name", name: "32486d83-4a38-42ba-afdb-f77ca40ea1fc") is the correct way to go... I tested using to_sql.. But can't answer as it marked as dup with a wrong language answer. - Arup Rakshit
@R_O_R -- I tested your second answer and it indeed works! Thanks so much. I wish I could accept your answer if someone hadn't wrongly marked my question as a duplicate. - dfinn

2 Answers

40
votes

You should call this as below :

Person.where("roles ? :name", name: "32486d83-4a38-42ba-afdb-f77ca40ea1fc")
13
votes

A workaround. Run this query to find out what function your operator maps to:

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

It yields

oprname  function
?        jsonb_exists(jsonb, text)
?        exist(hstore, text)

Now use the function instead of the operator in your query:

Person.where("jsonb_exists(roles, ?)", "32486d83-4a38-42ba-afdb-f77ca40ea1fc")