How to query json objects using Postgres JSONB column

Question

I have JSON stored in a jsonb column:

{
  "processedResult": {
    "orderPayment": {
      "paymentType": "VISA"
    },
    "store": "US"
  }
}

What I have tried:

SELECT DISTINCT myData -> 'processedResult' -> 'orderPayment' -> 'paymentType' 
FROM mytable
WHERE myData ->> 'processedResult' ->> 'store'  = 'US'

The WHERE clause seems to be incorrect.

Desired Output:

VISA
Mastedcard

Postgres Version: PostgreSQL 11.13

Answer 1

You'll want to use

SELECT DISTINCT myData -> 'processedResult' -> 'orderPayment' ->> 'paymentType' 
FROM mytable
WHERE myData -> 'processedResult' ->> 'store'  = 'US'

Notice that -> returns the selected jsonb value, whereas ->> always returns a postgres text value (or NULL, or an error).