CODEWITHSUNDEEP
jsonpostgresql
Eric Hideki
Eric Hideki

Reputation: 47

Subquery for element of JSON column

I have a big JSON data in one column called response_return in a Postgres DB, with a response like:

{ 
   "customer_payment":{ 
      "OrderId":"123456789",
      "Customer":{ 
         "Full_name":"Francis"
      },
      "Payment":{ 
         "AuthorizationCode":"9874565",
         "Recurrent":false,
         "Authenticate":false,
         ...
      }
   }
}

I tried to use Postgres functions like -> ,->> ,#> or @> to walk through headers to achieve AuthorizationCode for a query.

When I use -> in customer_payment in a SELECT, returns all after them. If I try with OrderId, it's returned NULL.

The alternatives and sources:

Using The JSON Datatype In PostgreSQL

Operator ->

Query for element of array in JSON column

This is not helpful because I don't want filter and do not believe that need to transform to array.

Upvotes: 1

Views: 272

Answers (1)

user330315
user330315

Reputation:

If you just want to get a single attribute, you can use:

select response_return -> 'customer_payment' -> 'Payment' ->> 'AuthorizationCode'
from the_table;

You need to use -> for the intermediate access to the keys (to keep the JSON type) and ->> for the last key to return the value as a string.

Alternatively you can provide the path to the element as an array and use #>>

select response_return #>> array['customer_payment', 'Payment', 'AuthorizationCode']
from the_table;

Online example

Upvotes: 3

Related Questions