Search code examples
postgresqljsonb

Selecting elements inside an array

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

Solution

  • 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'

    db<>fiddle