4

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"
Carpela
  • 2,155
  • 1
  • 24
  • 55

2 Answers2

6

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.

Carpela
  • 2,155
  • 1
  • 24
  • 55
2

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.
Community
  • 1
  • 1
Uzbekjon
  • 11,655
  • 3
  • 37
  • 54
  • The issue is that I'm absorbing the data from somewhere else (it's an email stored as json) so I really don't want to have duplicated parsed fields if there's a way to keep everything clean and I can do all the parsing on the db side). – Carpela Jun 28 '16 at 11:14
  • 1
    You could index the json field and convert it to a timestamp when you do, and as long as you convert the field when you order it, it will sort correctly. – Phill Jun 28 '16 at 11:53