Ordering Postgres jsonb in Rails

Question

I'm trying to order by a jsonb column in rails using:

Stat.order("data ->'likes'->'followed_by' ASC")

I keep getting this error returned.

PG::UndefinedFunction: ERROR:  could not identify an ordering operator for type json

I can't figure out is it my formatting? The followed_by attribute is a int.

Thank you!

jcuenod · Accepted Answer · 2015-04-02 20:47:06Z

12

I'm not certain but according to http://www.postgresql.org/docs/9.3/static/functions-json.html

-> "Get JSON object field"
->> "Get JSON object field as text"

So perhaps activerecord can't sort the field but can sort the text?

Try: Stat.order("data ->'likes'->>'followed_by' ASC")

answered Apr 2, 2015 at 20:47

jcuenod

58.9k15 gold badges71 silver badges103 bronze badges

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

1 Comment

Beartech Over a year ago

For others finding this; I needed to sort by a date in a jsonb field. Using "my_column ->'date' DESC" returned a text object. This worked because by date format is "YYYY-MM-DD". But I used the object notation and it worked as an actual date: "my_column ->>'date' DESC"

Collectives™ on Stack Overflow

Ordering Postgres jsonb in Rails

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related