postgresql将jsonb转换为记录集

Question

将jsonb转换为所需的表类型有问题，任何帮助将不胜感激。

我jsonb是：

{"left": ["ICMB", "IPERIOD", "IADDR", "KV"]} 

我想OT把它转换为：

position element 
left  ICMB 
left  IPERIOD 
left  IADDR 
left  KV 

提前感谢！

Answer 1

使用jsonb_each() and jsonb_array_elements_text():

with my_data(j) as (
values 
('{"left": ["ICMB", "IPERIOD", "IADDR", "KV"]}'::jsonb) 
) 

select key as position, element 
from my_data, 
lateral jsonb_each(j) as each(key, val), 
lateral jsonb_array_elements_text(j->key) as element; 

position | element 
----------+--------- 
left  | ICMB 
left  | IPERIOD 
left  | IADDR 
left  | KV 
(4 rows)