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"
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"}]';
j @> '{"ORDER_TYPE": "foo"}'?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.
If responsejsonb is really defined with the jsonb type, the cast to jsonb is useless.
