Question 113920 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to select a specific attribute from Postgres JSONB column

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"

Tags:

2

Answers


  1. 0

    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"}]';
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search