Sort by date in jsonb postgres

Question

I have a model where the data is stored in json format in a jsonb column in postgres.

I want to sort the output by a data field using an activerecord query.

Model.all.order("json_data -> 'date'")

gives me an output but orders it alphabetically based on the date string.

Is there an easy way I can sort this as a date?

Note: The dates are in the following format:

"Fri, 24 Jun 2016 04:13:26 -0700"

Answer 1

If the date is in a sensible format Postgres will deal with this automatically.

Model.all.order("(json_data ->> 'date')::timestamp with time zone DESC")

or

Model.all.order("(json_data ->> 'date')::timestamptz DESC")

If your date field string is a little unorthodox, you can do the following

Model.all.order("to_timestamp(json_data->>'date','Dy, DD Mon YYYY HH24:MI:SS ') DESC")

Details here

Note the ->> there to output the string rather than the json object.

You can of course just create an extra column and store your information there as per @Uzbekjon's answer below.