1

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

  • Allows you to select an element based on its name.
  • Allows you to select an element within an array based on its index.
  • Can be used sequentially: ::json->'elementL'->'subelementM'->…->'subsubsubelementN'.
  • Return type is json and the result cannot be used with functions and operators that require a string-based datatype. But the result can be used with operators and functions that require a json datatype.

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eric Hideki
  • 47
  • 6
  • Hi @a_horse_with_no_name, thanks for asking. My Postgres version is 11.6 and I need AuthorizationCode or Recurrent, for example. – Eric Hideki Nov 27 '19 at 20:49

1 Answers1

2

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