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

Question

In my postgresql DB I have the following query

select json_field from my_table where role='addresses_line';

returning

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

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}

then the desired second query is

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

thanks to @stefanov.fm

What if the array for alpha contains 100 elements instead of just one? — user330315
– user330315, Commented Oct 11, 2022 at 14:50

Stefanov.sm · Accepted Answer · 2022-10-11 14:55:25Z

2

Use jsonb_each function.

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"]}');

key	value
beta	10002
alpha	10001
gamma	10003

answered Oct 11, 2022 at 14:55

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

generalizing the question after it got the solution answer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

generalizing the question after it got the solution answer

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related