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

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 ?

CodePudding user response：

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