0

I have the following jsonb column in my table requests called tasks

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|         id  |           tasks                                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1           | [{"name":"Pick up","order":0,"end_at":null,"start_at":"2022-10-28T13:00:00.000+08:00"}, {"name":"Deliver","order":1,"end_at":"2022-10-28T13:42:00.000+08:00","start_at":"2022-10-28T13:27:00.000+08:00"}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Below is a sample of the table and i would like to get the start_at of the first object where order = 0

I could do this easily in postgres 12 but looking for a way to do it in postgres 11

select (jsonb_path_query_array(jsonb_agg(row_to_json(jb)), '$.tasks[0].start_at'))[0] from requests as jb where id = 1; This returns "2022-10-28T13:00:00.000+08:00"

How can i do this in postgres 11?

I tried using json_agg but could only get to getting the object but not the value select to_jsonb(jsonb_agg(row_to_json(jb))->0->'tasks'->>0)->>0 from requests as jb where id = 1;

Kingsley Simon
  • 2,090
  • 5
  • 38
  • 84

1 Answers1

0

Well, it is even simpler. First flatten the JSONB array.

select l ->> 'start_at' as start_at 
from requests, lateral jsonb_array_elements(tasks) as l
where l ->> 'order' = '0'
order by l ->> 'start_at' limit 1; -- get the first (oldest) one only

DB fiddle

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21