Suppose I have a jsonb array in a postgres column like
[{"startDate": "2019-09-01"}, {"startDate": "2019-07-22"}, {"startDate": "2019-08-08"}]
Is there a way to extract the earliest startDate from the jsonb array? I have tried using jsonb_array_elements but don't see how to loop through all the elements.
You can use a scalar sub-query
select (select (e.element ->> 'startDate')::date as start_date
from jsonb_array_elements(t.the_column) as e.element
order by start_date desc
limit 1) as start_date
from the_table t
You need to replace the_table and the_column with the actual table and column name you are using.
You can directly use MIN() aggregation after casting the derived value to date :
SELECT MIN((elm ->> 'startDate')::date)
FROM t
CROSS JOIN jsonb_array_elements(jsdata) AS j(elm)
