CODEWITHSUNDEEP
sqlarraysjsonpostgresqljsonb
Student
Student

Reputation: 297

Extract Earliest Date from Postgres jsonb array

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.

Upvotes: 2

Views: 578

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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)

Demo

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 4

Related Questions