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.


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';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.