How to use the key of jsonb in postgresql as a row value?

Question

I have a table like this:

id (serial) | data (jsonb)
1           | {"a": 1, "b": 2}
2           | {"a": 3, "b": 1}

how to convert it to this table:

id | dataKey | dataValue
1  | a       | 1             # {"a": 1} 
1  | b       | 2             # {"b": 2}
2  | a       | 3             # {"a": 3}
2  | b       | 1             # {"b": 1}

ps. character after # is comment

klin · Accepted Answer · 2017-08-21 09:13:36Z

1

Use jsonb_each().

with my_table(id, data) as (
values
(1, '{"a": 1, "b": 2}'::jsonb),
(2, '{"a": 3, "b": 1}')
)

select id, key, value
from my_table,
jsonb_each(data)

 id | key | value 
----+-----+-------
  1 | a   | 1
  1 | b   | 2
  2 | a   | 3
  2 | b   | 1
(4 rows)

answered Aug 21, 2017 at 9:13

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to use the key of jsonb in postgresql as a row value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related