How to order database columns in Rails when using MySQL database with json datatype?

Question

I have the model Job and Job model have field additional_data. Here additional_data column is stored as json data type and have field like "priority". I want to order jobs table entries based on additional_data's "priority" field value. I have code like following but it's not working. I need to order using ORM not by raw sql.

@jobs = Job.all.order('additional_data['priority'] asc')

Possible duplicate of MySQL Query Order By Value in JSON Column — infused
– infused, Commented Nov 2, 2016 at 17:16

Smek · Accepted Answer · 2017-03-27 10:38:49Z

0

This post is already 4 months old but maybe this can still make someone happy. You could do something like this.

@jobs = Job.all
.select('*, JSON_EXTRACT(additional_data, "$.priority") AS priority')
.order('priority asc')

answered Mar 27, 2017 at 10:38

Smek

1,20814 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

How to order database columns in Rails when using MySQL database with json datatype?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related