Postgresql JSONB Column, how to query for multiple specific elements in a JSON array

Question

Giving the following JSONB data structure of a stopInfo column in a myTable table:

{
 "stopInfo": [
 {
 "stopType": "Origin",
 "partnerId": "KR01",
 "locationId": "KR57",
 "partnerName": "Seller"
 },
 {
 "stopType": "Destination",
 "partnerId": "225735",
 "locationId": "0301223684",
 "partnerName": "Buyer"
 }
 ]
}

How do I write a single select statement to get the partnerName of the Origin stop and the Destination stop?

This is as close as I've come.

 select (jsonbColumn->'stopInfo'->>'partnerName') as OriginPartner, 
(jsonbColumn->'stopInfo'->>'partnerName') as DestinationPartner
 from myTable;

Clearly there's some kind of in-line criteria I need to apply for each column to ensure I'm getting the value out of the right array element, but I'm struggling to find a good example of this simple scenario.

Answer 1

I figured it out after stitching together a few other questions on Stack Exchange, mainly this one: How to get particular object from jsonb array in PostgreSQL?

select origin.value->>'partnerName' as OriginPartner, 
destination.value->>'partnerName' as DestinationPartner
from myTable
join lateral jsonb_array_elements(stopInfo) origin(value) ON origin.value->>'stopType' = 'Origin'
join lateral jsonb_array_elements(stopInfo) destination(value) ON destination.value->>'stopType' = 'Destination';