Posgres - Order by field within JSONB array?

user_2130426 0
Viewed 79

Here is my data sample of my jsonb field itineraries in a row of my table trips:

[
    {
        "nights": 10,
        "destinations": [
            "Cronaside",
            "New Emory",
            "East Mattiechester"
        ],
        "departure_date_end": "2020-09-21",
        "return_date_latest": "2020-10-01",
        "departure_date_start": "2020-09-14"
    },
    {
        "nights": 10,
        "destinations": [
            "Port Verdie",
            "Chaunceymouth",
            "Isabellemouth"
        ],
        "departure_date_end": "2020-10-08",
        "return_date_latest": "2020-10-18",
        "departure_date_start": "2020-09-14"
    }
]

My aim is to return rows from trips ordered by the earliest departure_date_start of this jsonb field itineraries. Here is my simple attempt:

SELECT * 
from trips
ORDER BY itineraries->>'departure_date_start' ASC

However, because itineraries is an array and can have multiple departure_date_start values, does Postgres handle this automatically? Or am I required to loop over the array?

Using Postgres 9.6

jsonb postgresql
1 Answers

You need to expand the array.

select t.*
  from trips t
 cross join lateral jsonb_array_elements(t.itineraries) as i(datel)
 order by i.datel->>'departure_date_start';

This will return multiple rows per trip.

If you want to limit the result to only one row per trip, then you can group by the columns you are returning like so:

select t.id, t.some_col, t.some_other_column, t.itineraries, 
       min(i.datel->>'departure_date_start') as departure_date_start
  from trips t
 cross join lateral jsonb_array_elements(t.itineraries) as i(datel)
 group by t.id, t.some_col, t.some_other_column, t.itineraries
 order by min(i.datel->>'departure_date_start')
user_138083190
Related