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

Bergi · Accepted Answer · 2022-11-13 22:07:43Z

6

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).

answered Nov 13, 2022 at 22:07

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1