Search code examples
postgresqlsql-order-byjsonb

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

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?

Solution

  • 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