How to select a specific attribute from Postgres JSONB column-CodePudding

I have JSON stored in a jsonb column:

[
  {
    "ORDER_TYPE": "foo",
    "PAYMENT_TYPE": "VISA",
  }
]

I can query fine but is it possible to select specific parts (PAYMENT_TYPE) of the json from the resultset?

SELECT PAYMENT_TYPE 
FROM tools.orders 
WHERE responsejsonb::jsonb @> '[{"ORDER_TYPE":"foo"}]';

Desired output "PAYMENT_TYPE": "VISA" or "VISA"

CodePudding user response：

Flatten the JSONB array first.

select j ->> 'PAYMENT_TYPE' as payment_type -- and other expressions?
from tools.orders
cross join lateral jsonb_array_elements(responsejsonb::jsonb) as l(j)
where j ->> 'ORDER_TYPE' = 'foo';

Edit
If however the responsejsonb array has only one element (or only the first one matters) then it's simpler, the expression that you need is responsejsonb::jsonb->0->>'PAYMENT_TYPE'.

SELECT responsejsonb::jsonb->0->>'PAYMENT_TYPE'
FROM tools.orders 
WHERE responsejsonb::jsonb @> '[{"ORDER_TYPE":"foo"}]';

CodePudding user response：

You can use a JSON path expression:

SELECT jsonb_path_query_first(responsejsonb, '$[*] ? (@.ORDER_TYPE == "foo").PAYMENT_TYPE')
FROM tools.orders 
WHERE responsejsonb @> '[{"ORDER_TYPE":"foo"}]';

If you want all payment types, use jsonb_path_query_array() instead.

Online example

If responsejsonb is really defined with the jsonb type, the cast to jsonb is useless.