Creating an active record scope to order on Jsonb Field

Question

I have a table called sessions with a jsonb attribute called lms_data. I want to create a scope to order by a specific field in the jsonb column. The following query runs fine select * from sessions ORDER BY lms_data->>'startDate' ASC However the following scope in the Session model wont execute :

  scope :order_by_start_date_asc, -> { order("lms_data->>'startDate' ASC ") }

It fails with Query method called with non-attribute argument(s): "lms_data->>'startDate' ASC "

How can I create a scope that orders based on a field in a jsonb column ?

Answer 1

As of Rails 6 Raw SQL is disallowed by order referring to it as a "dangerous query method"; however the deprecation warning also offered that "Known-safe values can be passed by wrapping them in Arel.sql()" so we can work around your error by creating an Arel::SqlLiteral like so

scope :order_by_start_date_asc, -> { order(Arel.sql("lms_data->>'startDate'").asc) }

We could convert the inner string into a node all of its own but this should get you over the hurdle in question.

To convert this to full Arel you can use

order(
  Arel::Nodes::InfixOperation.new('->>',
    Arel.sql('lms_data'),
    Arel.sql("'start_date'")
).asc)