Postgres jsonb order rows in the ascending order of a jsonb value

Question

I have a jsonb field in my table called 'day_parting'. It looks like this:

[{"end_time": 12, "start_time": 0}, {"end_time": 18, "start_time": 17}]

It has an array of values, that represent the various start time & end time.

I'm doing a query that joins & filters on two tables and whatever the result I get, I want it ordered in the ascending order of the day_parting start times.

Eg:

ad1, [{"end_time": 12, "start_time": 11}, {"end_time": 18, "start_time": 17}]
ad2, [{"end_time": 12, "start_time": 7}, {"end_time": 18, "start_time": 13}]
ad3, [{"end_time": 5, "start_time": 4}]

On running the query, the ordering should be:

ad3, [{"end_time": 5, "start_time": 4}]
ad2, [{"end_time": 12, "start_time": 7}, {"end_time": 18, "start_time": 13}]
ad1, [{"end_time": 12, "start_time": 11}, {"end_time": 18, "start_time": 17}]

I have created a db fiddle for my scenario. I have been reading postgres jsonb documentation and here is where I've reached:

select a.*, lateral (select min((dp->>'start_time')::int) x from jsonb_array_elements(a.day_parting) dp) t(x) from merchant_ads a join merchant_devices d on not (d.blacklisted_tags && a.tags) and not (d.blacklisted_brands && array[a.brand::text]), jsonb_array_elements(a.day_parting) where d.device_id = 'device6' and a.active = true and (date(now()) BETWEEN a.start_date AND a.end_date) order by x;

This isnt working and hence my question here, how do I get the ascending ordering such that I order the rows by the start_time of the jsonb values.

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-06-18T08:13:07

You could use an ORDER BY clause like this:

ORDER BY (SELECT min(CAST(j->>'start_time' AS integer))
          FROM jsonb_array_elements(day_parting) AS elem(j))

Postgres jsonb order rows in the ascending order of a jsonb value

1 Answers