CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordjsonb
Carpela
Carpela

Reputation: 2195

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"

Upvotes: 4

Views: 3846

Answers (2)

Carpela
Carpela

Reputation: 2195

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.

Upvotes: 6

Uzbekjon
Uzbekjon

Reputation: 11813

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

Not as part of the jsonb field, since JSON doesn't know anything about dates.

So, an easy alternative would be to store them as a separate table column.

If you really have to store them as an element of your json field, then I would suggest one of the two options:

  1. Store your field as timestamp. Since, json fields in postgresql support numeric values and it could (potentially) optimize the sorting.
  2. Store your date in ISO 8601 format string. It would sort correctly, even if it is a string.

Upvotes: 2

Related Questions