Mysql 5.7 json数据类型，在Rails 5中使用activerecord进行查询

Question

我正在用Rails 5 rc1做一个应用程序。Rails5支持MySQL5.7 json数据类型。

add_column :organizations, :external, :json

假设此列中的值如下：

+---------------------------+
| external                  |
+---------------------------+
| {"id": 10, "type": "mos"} |
+---------------------------+

为了在external列中搜索特定的"id“和"type”，我在mysql中使用以下查询：

select external from organizations where JSON_CONTAINS(external,'{"id": 10, "type": "mos"}') ;

现在，我想知道如何使用rails进行相同的查询。以下代码不起作用：

Organization.where("JSON_CONTAINS(external,'{"id": 10, "type": "mos"}')")

注意:我不能删除json文本周围的引号，因为它是查询的一部分。

Answer 1

您仍然可以利用ActiveRecord的where方法并绑定变量，而无需求助于find_by_sql。

Organization.where("external->'$.id' = :id and external->'$.type' = :type", id: 10, type: "mos")

Answer 2

使用MYSQL，您可以使用JSON_EXTRACT从存储在字段中的JSON对象中提取值。在你的情况下，试着使用...

Organization.where("JSON_EXTRACT(external, '$.id') = 10 AND JSON_EXTRACT(external, '$.type') = 'mos'")

只是为了好玩，在伪代码中...

<Class>.where("JSON_EXTRACT(<column_header>, '$.<object key>') <comparison operator> <object value>")

这应该可以完成工作，尽管可能有一种更漂亮的方式来编写它:)

Answer 3

如下所示：

id_field = '$."id"'
type_field = '$."type"'    
Organization.where("JSON_UNQUOTE(json_extract(external, '#{id_field}')) = ? AND JSON_UNQUOTE(json_extract(external, '#{type_field}')) = ?", 10, "mos")