Search code examples
ruby-on-railspostgresqlactiverecordjsonb

Sort by date in jsonb postgres

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

  • 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.