-1

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.

Improve this question

1 Answer 1

Reset to default
0

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 and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.