如何逃脱? (问号)运算符在Rails中查询PostgreSQL JSONB类型 [英] How to escape the ? (question mark) operator to query Postgresql JSONB type in Rails

问题描述

我正在使用Rails 4.2和Postgres 9.4尝试新的JSONB数据类型.我数据库中的JSONB列之一包含一个数组，我希望能够查询该数组包含某个值的记录.我想出了如何使用新的JSONB问号"("contains")运算符执行此操作，如此处所示: http://www.postgresql.org/docs/9.4/static/functions-json.html

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

因此，在原始SQL中，我可以像下面的示例那样使它工作:

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

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

但是我看不到通过ActiveRecord在Rails中进行此查询的任何方法.我尝试使用"where"方法进行原始查询，如下所示:

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:绑定变量的数量错误 (1 for 2)在:角色? ?

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")

这篇关于如何逃脱? (问号)运算符在Rails中查询PostgreSQL JSONB类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！