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!
You should call this as below :
Person.where("roles ? :name", name: "32486d83-4a38-42ba-afdb-f77ca40ea1fc")