SQL: extract keys and values of a jsonb field as rows of two separate columns

In my postgresql DB I have the following query

select json_field from my_table where role='addresses_line';

select json_field from my_table where role='addresses_line';

​

returning

{"alpha": ["10001"], "beta": ["10002"], "gamma": ["10003"]}

{"alpha": ["10001"], "beta": ["10002"], "gamma": ["10003"]}

​

where json_field is of type JSONB.

I am looking for a way to query all the keys and all the values from this json field, so that the output would be

  key  |  value
----------------
alpha  |  10001
beta   |  10002
gamma  |  10003

  key  |  value

----------------

alpha  |  10001

beta   |  10002

gamma  |  10003

​

Is there a way to do it ?

generalizing the question after it got the solution answer

When I wrote the question I did not paid attention, so I implicitly asked not only to extract keys and values of a jsonb field as rows of two separate columns, but also – assuming that all the values associated to the key were 1 element-long arrays – to select only the value of the first element of the list in the value associated to each key.
The solution given by stefanov.fm does so.

Now, to generalize the answer, in case the first query result were

{"alpha": 10001, "beta": 10002, "gamma": 10003}

{"alpha": 10001, "beta": 10002, "gamma": 10003}

​

then the desired second query is

select key, value
from jsonb_each
(
  (select json_field from my_table where role='addresses_line')
);

select key, value

from jsonb_each

(

  (select json_field from my_table where role='addresses_line')

);

​

thanks to @stefanov.fm

Answer

Use jsonb_each function.

select key, (value ->> 0) as value
from jsonb_each
(
  (select json_field from my_table where role='addresses_line')
);

select key, (value ->> 0) as value

from jsonb_each

(

  (select json_field from my_table where role='addresses_line')

);

​

Here is an illustration with your data as a literal.

select key, (value ->> 0) as value 
from jsonb_each('{"alpha": ["10001"], "beta": ["10002"], "gamma": [10003"]}');

select key, (value ->> 0) as value 

from jsonb_each('{"alpha": ["10001"], "beta": ["10002"], "gamma": [10003"]}');

​