json - 在 PostgreSQL 12 中提取 JsonB 字典的元素

Question

我看过类似的问题，但我找不到如何将字典存储在：

表A：id int，数据jsonb

For example:

id = 1
data = {"Key1": 1, "Key2": "a2", "Key3": [3, 4]}

到表 B：id int、key text、payload jsonb

Using the same example as above, I would get the 3 records:

id  Key    payload
--------------------
1   Key1        1
1   Key2   "a2"
1   Key3   [3, 4]

提前感谢您的帮助！

标签： jsonpostgresqldictionaryjsonb

Answer 1

使用jsonb_each()：

insert into table_b
select id, key, payload
  from table_a
 cross join lateral jsonb_each(data) as e(key, payload);