I have this JSON and would like to filter by WHERE installmentType = 'STANDARD'

In the SELECT clause I would like to return the region attribute.

{
    "processedResult": {
        "TYPE": "ACKNOWLEDGEMENT",
        "orderPayment": {
            "paymentDetails": [
                {
                    "installmentPayment": {
                        "installmentType": "STANDARD"
                    }
                }
            ]
        },
        "region": "US"
    }
}

Desired Output:

What I've tried so far but this just gives me the paymentDetails block:

SELECT arr.item_object
FROM aosqe_ema_tools.ocs_response t,
jsonb_array_elements(t.ocsjsonb -> 'processedResult' -> 'orderPayment' -> 'paymentDetails') 
with ordinality arr(item_object, position)

Postgres Version : PostgreSQL 11.13

CodePudding user response：

Use item_object in the WHERE clause:

select 
  ocsjsonb -> 'processedResult' ->> 'region' as region,
  ocsjsonb -> 'processedResult' ->> 'TYPE' as type
from ocs_response
cross join jsonb_array_elements(
  ocsjsonb -> 'processedResult' -> 'orderPayment' -> 'paymentDetails') 
    as arr(item_object)
where item_object -> 'installmentPayment' ->> 'installmentType' = 'STANDARD'

Test it in db<>fiddle.