Order a table by a key in an array of json (stored as jsonb) in Postgres

0
votes

I have a postgres which has a jsonb column which is an array of jsons. 

id  array_json
123 [{"end_time": 22, "start_time": 21}]
124 [{"end_time": 22, "start_time": 9}, {"end_time": 20, "start_time": 0}]
126 [{"end_time": 22, "start_time": 0}]
125 [{"end_time": 22, "start_time": 1}, {"end_time": 20, "start_time": 6}]

I want to order these by the "start_time". A row has an array of jsons. In such cases, the ordering has to be considering the earliest start time in the entire array. If two arrays have the same start time, it doesnt matter which one comes first. The final result should be:

id  array_json
126 [{"end_time": 22, "start_time": 0}]
124 [{"end_time": 22, "start_time": 9}, {"end_time": 20, "start_time": 0}]
125 [{"end_time": 22, "start_time": 1}, {"end_time": 20, "start_time": 6}]
123 [{"end_time": 22, "start_time": 21}]

I've created a fiddle of this table. How should this be done?

2
postgresqlsql-order-byjsonb
#user248884 - Do you have any end_time or start_time column ? – Zakir Hossain
No. start_time & end_time are keys of my json. The column name is 'array_json' – user248884

2 Answers

1
votes

This will work I think: 

select id, array_json from the_table,
lateral (select min((d->>'start_time')::int) x from jsonb_array_elements(array_json) d) t(x)
order by x

Best regards,
Bjarni

0
votes

This query can be used:

select id, min(start_time) min_start_time, array_json 
from (
   select 
       id, 
       array_json,
       -- we must convert start_time to integer for right sort order
       (jsonb_array_elements(array_json)->>'start_time')::int4 as start_time
    from the_table
) tmp 
group by id, array_json
order by min_start_time;