Mysql 5.7 json datatype, query with activerecord in Rails 5

Question

I am making an application with Rails 5 rc1. Rails 5 support mysql 5.7 json datatype.

add_column :organizations, :external, :json

Suppose the value in this column is as follows:

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

To search a particular "id" and "type" in external column, I use the following query in mysql:

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

Now, I want to know, how to make the same query using rails. The following doesn't work:

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

Note: I cannot remove quotes around the json text as it is part of the query.

Answer 1

You can still leverage ActiveRecord's where method and bind variables without resorting to find_by_sql.

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

Answer 2

With MYSQL you can use JSON_EXTRACT to pull values from a JSON object stored in a field. In your case, try using...

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

and just for kicks, in pseudo code...

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

This should get the job done, although there may be a prettier way to write it :)