I have this JSON and would like to select data from the array section:
{
"processedResult": {
"orderPayment": {
"paymentType": "VISA"
},
"store": "US",
"orderDeliveryGroups": [
{
"address": {
"city": "New York",
"state": "NY"
},
"deliveryType": "STH"
}
]
}
}
What I have now
SELECT DISTINCT myDataJsonb -> 'processedResult' -> 'orderPayment' -> 'paymentType'
FROM myData
WHERE myDataJsonb -> 'processedResult' ->> 'store' = 'US'
Desired Output:
PaymentType | DeliveryType |
---|---|
VISA | STH |
AMEX | STH |
PayPal | FOO |
Postgres Version : PostgreSQL 11.13
CodePudding user response:
try this
select t.data->'processedResult'->'orderPayment'->'paymentType' as paymentType,
jsonb_array_elements( t.data->'processedResult' -> 'orderDeliveryGroups')->>'deliveryType'
as deliveryType from table_name t
WHERE t.data -> 'processedResult' ->> 'store' = 'US'