使用鍵和值將兩個 Json 數組合併為一個

我有兩個 jsonb 列（鍵、值）。例如：鍵列值 =["key1","key2","key3","key4"] 值列 =["val1","val2","val3","val4"]

我想編寫一個選擇查詢以根據數組索引獲得如下輸出。

{"key1":"val1","key2":"val2","key3":"val3","key4":"val4"}

您需要取消嵌套數組，然後將所有內容聚合回單個 JSON 值：

select x.value
from the_table t
 cross join lateral (
   select jsonb_object_agg(k.ky, v.value) as value
   from jsonb_array_elements_text(t.keys) with ordinality as k(ky,idx)
      join jsonb_array_elements(t.values) with ordinality as v(value,idx) on k.idx = v.idx
 ) x

線上範例

引用自：https://dba.stackexchange.com/questions/291088