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.

Newton Liebniz · Accepted Answer · 2016-07-11 04:58:27Z

17

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

answered Jul 11, 2016 at 4:58

Newton Liebniz

2141 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ashish Agrawal Over a year ago

This is only applicable for postgresql and not for mysql.

Suganya Over a year ago

Works for me in Rails 5.1 and Mysql 5.7

sekrett Over a year ago

Does not work on MariaDB 10.4, but JSON_EXTRACT() does.

Adrien Over a year ago

Still a valid answer with MySQL 8 and Rails 6

sammms · Accepted Answer · 2017-06-29 22:31:34Z

9

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

edited Jun 29, 2017 at 22:31

answered Jun 29, 2017 at 22:22

sammms

6878 silver badges25 bronze badges

1 Comment

sekrett Over a year ago

I like this more because it works both on MySQL and MariaDB.

rainstop3 · Accepted Answer · 2016-11-08 09:55:49Z

2

Like this:

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

answered Nov 8, 2016 at 9:55

rainstop3

1,44811 silver badges13 bronze badges

Comments

Ashish Agrawal · Accepted Answer · 2016-05-31 17:56:26Z

0

I did not find any solution through activerecord as of now. An alternative way to query is as follows:

type = "mos"
id = 10

organization = Organization.find_by_sql(["select * from organizations where JSON_CONTAINS(external_ref ,'{\"id\": ?, \"type\": \"#{ActiveRecord::Base::sanitize(type).remove("'")}\"}')", id]).first

Once there is a solution with activerecord query interface, I will update.

answered May 31, 2016 at 17:56

Ashish Agrawal

3911 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Mysql 5.7 json datatype, query with activerecord in Rails 5

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related